December 19, 2010 at 7:44 am
Hi,
I am facing some problem while updating a column through trigger.
I have a table User_Records with n number of columns, one of the column is SequenceNumber. When first time the rows are inserted into this table SequenceNumber is stored as null. Once the rows in the table are verified by the user the sequence number of verified row is updated to the max(SequenceNumber) + 1. The records which are not verified will have SequenceNumber as null. The trigger to update the table is as below.
ALTER TRIGGER update_sequence_no
ON User_Records
AFTER UPDATE
AS
DECLARE @SequenceNumber AS INT
IF UPDATE (VERIFIED_TRANSACTION)
BEGIN
IF EXISTS (SELECT * FROM INSERTED WHERE INSERTED.VERIFIED_TRANSACTION = '0')
BEGIN
SELECT @SequenceNumber = MAX(ISNULL(SequenceNumber, 0)) FROM User_Records
SET @SequenceNumber = @SequenceNumber + 1
UPDATE User_Records SET ORDERS.SequenceNumber = @SequenceNumber
FROM INSERTED WHERE User_Records.ORDER_ID = INSERTED.ORDER_ID
END
END
GO
Now the problem is, sometimes (once in 100 records) the SequenceNumber is skipped i.e. after 53, 54 is skipped and the next row has 55.
Any help on this regards will be appreciated.
Thanks
December 19, 2010 at 8:37 am
I cant use Identity column to solve this because I will be having null values also in the column.
Thanks
Pankaj
December 19, 2010 at 10:32 am
pankaj.nainu1 (12/19/2010)
Hi,I am facing some problem while updating a column through trigger.
I have a table User_Records with n number of columns, one of the column is SequenceNumber. When first time the rows are inserted into this table SequenceNumber is stored as null. Once the rows in the table are verified by the user the sequence number of verified row is updated to the max(SequenceNumber) + 1. The records which are not verified will have SequenceNumber as null. The trigger to update the table is as below.
ALTER TRIGGER update_sequence_no
ON User_Records
AFTER UPDATE
AS
DECLARE @SequenceNumber AS INT
IF UPDATE (VERIFIED_TRANSACTION)
BEGIN
IF EXISTS (SELECT * FROM INSERTED WHERE INSERTED.VERIFIED_TRANSACTION = '0')
BEGIN
SELECT @SequenceNumber = MAX(ISNULL(SequenceNumber, 0)) FROM User_Records
SET @SequenceNumber = @SequenceNumber + 1
UPDATE User_Records SET ORDERS.SequenceNumber = @SequenceNumber
FROM INSERTED WHERE User_Records.ORDER_ID = INSERTED.ORDER_ID
END
END
GO
Now the problem is, sometimes (once in 100 records) the SequenceNumber is skipped i.e. after 53, 54 is skipped and the next row has 55.
Any help on this regards will be appreciated.
Thanks
-Can you tell me why this column is nullable in the first place ?
Why don't you just put it at zero (0) for initial value ! (and make the column NOT NULL please)
- What's the relation of sequence :
-- at row level, table level ?
- Are you updating the initial row or are you inserting a new row ?
- how about replacing that with e.g. DateTimeLastUpdate ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 19, 2010 at 12:07 pm
pankaj.nainu1 (12/19/2010)
Hi,I am facing some problem while updating a column through trigger.
I have a table User_Records with n number of columns, one of the column is SequenceNumber. When first time the rows are inserted into this table SequenceNumber is stored as null. Once the rows in the table are verified by the user the sequence number of verified row is updated to the max(SequenceNumber) + 1. The records which are not verified will have SequenceNumber as null. The trigger to update the table is as below.
ALTER TRIGGER update_sequence_no
ON User_Records
AFTER UPDATE
AS
DECLARE @SequenceNumber AS INT
IF UPDATE (VERIFIED_TRANSACTION)
BEGIN
IF EXISTS (SELECT * FROM INSERTED WHERE INSERTED.VERIFIED_TRANSACTION = '0')
BEGIN
SELECT @SequenceNumber = MAX(ISNULL(SequenceNumber, 0)) FROM User_Records
SET @SequenceNumber = @SequenceNumber + 1
UPDATE User_Records SET ORDERS.SequenceNumber = @SequenceNumber
FROM INSERTED WHERE User_Records.ORDER_ID = INSERTED.ORDER_ID
END
END
GO
Now the problem is, sometimes (once in 100 records) the SequenceNumber is skipped i.e. after 53, 54 is skipped and the next row has 55.
Any help on this regards will be appreciated.
Thanks
The apparent skips are probably for the same reason that an IDENTITY column would have skips... someone does something... the triggers fire to assign a number... someone else does the same and then the first transaction is rolled back for some reason.
My biggest curiosity is that this trigger is written to handle just one row (RBAR) at a time. If someone ever decides to do a batch job on, say, 100 rows, only the first row will get a sequence number. And, planned for or not, "batch happens". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2010 at 11:22 pm
-Can you tell me why this column is nullable in the first place ?
I can make it not null, but how this is gonna help?
Why don't you just put it at zero (0) for initial value ! (and make the column NOT NULL please)
This can be done.
- What's the relation of sequence :
-- at row level, table level ?
This sequence number has no relation with any other row or table in the DB. This number is used in other accounting software which takes data from this table.
- Are you updating the initial row or are you inserting a new row ?
First the row is inserted and on verification the SequenceNumber of that row is updated.
- how about replacing that with e.g. DateTimeLastUpdate ?
I cant do that because the accounting software requires the number to be in a series. (e.g 150, 151, 152, 153, 154, 155 ......)
The strange thing is that I do have an identity column in the table which does not have any skips. What comes to my mind is that in the trigger somehow MAX(ISNULL(SequenceNumber, 0)) is not getting the correct value.
December 19, 2010 at 11:31 pm
pankaj.nainu1 (12/19/2010)
The strange thing is that I do have an identity column in the table which does not have any skips. What comes to my mind is that in the trigger somehow MAX(ISNULL(SequenceNumber, 0)) is not getting the correct value.
Ooookkkkk... so why the heck do you need error prone sequence logic in a RBAR trigger if you have an identity column in the table? This makes no sense at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2010 at 11:46 pm
Ooookkkkk... so why the heck do you need error prone sequence logic in a RBAR trigger if you have an identity column in the table? This makes no sense at all.
I cant use identity column because not all the transactions are valid and for invalid transactions the SequenceNumber will be null or 0.
Thanks
Pankaj
December 20, 2010 at 1:32 am
So you cannot afford gaps in this sequence number.
IMO that calls for a processing model that only operates on committed rows or sets.
Meaning, log your action and process the sequence number allocation out of the original transaction.
e.g. a sqlagent job in background or a service broker solution
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 20, 2010 at 2:12 am
Yes i did thought of a sql agent job, but want to know if this can be achieved by some kind of locking or something else. I want to keep sql agent job as the last option.
December 20, 2010 at 2:35 am
Since you cannot afford gaps, you cannot rely on transactions that may still be rolled back.
( for reasons over which you have no control ) there can be multiple ways, but all will be out of the original transaction scope !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 20, 2010 at 3:55 am
I have not done sequence counters with triggers, but I can tell you there is a fundamental weakness with using MAX(ISNULL(SequenceNumber, 0)) for generating sequence numbers that you then update/insert into your database. It is very prone to race conditions that can lead to the type of behavior you are seeing. I would never use that MAX() logic you are describing; I've been down that road and it WILL break on you at some point.
What do I mean by "race conditions?" If two clients or processes call the action at roughly the same time, they both will get the same value for "MAX" and get handed the same sequence number. Duplicate sequence numbers can then, depending on how your app works, violate various constraints you have and lead to gaps or errors. I've run into this due to browser double-clicks, low-level TCP-IP retransmissions where TCP/IP packet ACKs aren't received by the client's OS so the client's OS regenerates the packets but the server OS got both sets of input and thus called the server app/database twice, etc. And there are a dozen other ways you can get race conditions.
If you are generating sequence numbers and don't want to use identity columns, you need a mechanism that guarantees transactional integrity of your "max+1" operation. I suggest creating a sequence table, and having a stored procedure that increments (updates) that column in that table with a colname=colname+1, and returns the resulting sequence value (determined via an additional select, if I recall correctly) as an output parameter. And ensure that stored procedure has sufficient transaction/table locking parameters that only one process can run through the transactional block of "increment operator and subsequent select/assignment operator" at a time. (And ensure that no other logic besides that stored procedure operates on that table, just use it for that purpose alone.)
If you need multiple sequence numbers, that sequence table can have multiple columns, and your sequence-incrementing stored procedure can update different ones depending on the parameters passed to it.
I would also add that the above mechanism doesn't completely eliminate the possibility of gaps if your sequence generator stored procedure's return value isn't then inserted/updated in the tables you are using. If you really want to ensure no gaps, then whenever you would be updating the sequence number in your application's tables, you would need to wrap your sequence-table-number-incrementing logic together when your application-setting-sequence-number into a single BEGIN/COMMIT transaction. Yes, this does imply that you need to move this logic out of a trigger and into your application. Perhaps someone wiser than me can see a way around that, but at a fundamental level, to guarantee the properties you are talking about, it seems to me that you need to wrap the "read persistent counter, increment persistent counter, set database value to the counter" operations all within a single (ACID) transaction. Good luck.
December 20, 2010 at 4:11 am
Hi gregw_lists,
I have removed Max query and replaced it with the one below.
SELECT TOP 1 @UserDefinedSequenceNumber = UserDefinedSequenceNumber FROM ORDERS
WHERE UserDefinedSequenceNumber IS NOT NULL ORDER BY UserDefinedSequenceNumber DESC
Still there are gaps in the sequence number.
Can you suggests me something else which will work?
Thanks
Pankaj
December 20, 2010 at 4:30 am
Sorry, I posted accidentally and just finished re-editing it and now I see you have replied. See that post. TOP will have the same problems as MAX.
December 20, 2010 at 9:06 am
pankaj.nainu1 (12/19/2010)
Ooookkkkk... so why the heck do you need error prone sequence logic in a RBAR trigger if you have an identity column in the table? This makes no sense at all.
I cant use identity column because not all the transactions are valid and for invalid transactions the SequenceNumber will be null or 0.
Thanks
Pankaj
Wouldn't it be easier just to add an "IsValid" column?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 20, 2010 at 9:36 am
In case I'm missing the obvious here ---
Is there any way you could use row_number at SELECT time for this column?
if not, I don't see any better way than a job that only processes "validated" data after the transaction is committed.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply