January 16, 2018 at 1:56 pm
This is more of a question about how best to handle something versus the technical implementation.
When 1 or more rows in a table are updated I need to check for the value in 2 fields and if they meet a condition I need to change one of these fields but I need to do so without interfering the existing update.
Pseudo Example: Table name is UNIT
If INSERTED.sCol01 = 'NewValue' AND DELETED.sCol01 <> INSERTED.sCol1 AND IINSERTED.iCol01 = 0
SET UNIT.iCol01 = 1
END IF
The issue is that UNIT.iCol01 is not always being updated when UNIT.sCol01 is being set to 'NewValue' and so when this happens we need to explicitly set UNIT.iCol01 to 1. I could use an After Update trigger to do this but if it fires and does have to update UNIT.iCol01 the wouldn't that cause the After Update trigger to fire yet again? Granted oj the second firing it would not perform an update because the update already occurred but this feels like were doing 2 updates when we should only need to do 1 update.
If I use an UPDATE trigger I don't want to interfere with or have to mange the existing update.
In summary I guess the question is how best to passively update a field in a table that may not be part of the initial update and that does not alter the existing update and that ideally does not cause an additional updates.
Kindest Regards,
Just say No to Facebook!January 16, 2018 at 3:28 pm
Yes, the trigger will fire again.
The easiest way around that is to use CONTEXT_INFO to set a "flag byte" to "tell" the trigger not to update on the second pass, it just resets the flag.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply