September 5, 2018 at 7:31 am
I have to convert a trigger that was in a DB2 database to T-SQL and am really struggling with this one since there is no Before Update option for T-SQL. I don't have much experience writing these so feeling very lost now.
Here is the existing DB2 trigger
CREATE TRIGGER BC_OFFERLIST1
NO CASCADE BEFORE UPDATE ON OFFERLIST
REFERENCING OLD AS O
NEW AS N
FOR EACH ROW
MODE DB2ROW
BEGIN ATOMIC IF
( N . TIMESTAMP +
( TIMESTAMP
( '20020101000001' ) - TIMESTAMP
( '20020101000000' )
) < O . TIMESTAMP
) THEN SIGNAL SQLSTATE 'UPERR' ( 'The update is not possible' ) ;
ELSE SET N . TIMESTAMP = CURRENT TIMESTAMP ;
END IF ;
END;
Would appreciate some suggestions.
September 5, 2018 at 7:35 am
First you need to understand what the db2 trigger is doing. It looks like it is using the timestamp data type (or rowversion) and checking the upper limits of it. SQL will automatically do this if you set the column to be data type rowversion, it will get automatically updated whenever the row is modified and the trigger isn't needed.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 5, 2018 at 8:32 am
Thanks for your reply Mike01.
With rowversion the application would have to be aware of it and we have to make the change without any changes to the application.
September 5, 2018 at 11:07 am
Butters - Wednesday, September 5, 2018 8:32 AMThanks for your reply Mike01.
With rowversion the application would have to be aware of it and we have to make the change without any changes to the application.
If you know the code to get the equivalent then use an instead of trigger for the check and update in SQL Server. That's as close as you can get to a before update trigger.
Sue
September 5, 2018 at 12:09 pm
Sue_H - Wednesday, September 5, 2018 11:07 AMButters - Wednesday, September 5, 2018 8:32 AMThanks for your reply Mike01.
With rowversion the application would have to be aware of it and we have to make the change without any changes to the application.If you know the code to get the equivalent then use an instead of trigger for the check and update in SQL Server. That's as close as you can get to a before update trigger.
Sue
UPDATE triggers will work in a similar way to a BEFORE trigger, but you will need to make sure you also perform the actual UPDATE of all the other columns as well, not just the TIMESTAMP column.
September 5, 2018 at 12:25 pm
Chris Harshman - Wednesday, September 5, 2018 12:09 PMSue_H - Wednesday, September 5, 2018 11:07 AMButters - Wednesday, September 5, 2018 8:32 AMThanks for your reply Mike01.
With rowversion the application would have to be aware of it and we have to make the change without any changes to the application.If you know the code to get the equivalent then use an instead of trigger for the check and update in SQL Server. That's as close as you can get to a before update trigger.
Sue
UPDATE triggers will work in a similar way to a BEFORE trigger, but you will need to make sure you also perform the actual UPDATE of all the other columns as well, not just the TIMESTAMP column.
As I said, Instead of trigger is still the closest to a DB2 before update trigger as in DB2 a before update trigger is used to check a condition BEFORE doing the update. As opposed to doing the update, check a condition, rollback.
Duration would be shorter if checking the condition first and skipping the update if the condition isn't met.
Sue
September 5, 2018 at 6:15 pm
TIMESTAMP in DB2 is not a row version, it's actually a date-time.
What the trigger seems to be doing is preventing consequitive updates within 1second.
The implementation depends on how you want to deal with situations when some of the updated rows are ok to proceed, and other have to be blocked.
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply