JohnG (9/5/2008)
Hugo Kornelis (9/5/2008)
That's why many developers make it a habit to start each trigger with this line:
IF @@ROWCOUNT = 0 RETURN;Do make sure that it's the very first line of the trigger, though.
DO NOT use this in a trigger to test if any rows need to be processed by the trigger.
Although this technique has been documented in independent books (e.g., Ken Henderson) the value could be zero from some other action performed by some other trigger. I.e., the value of @@ROWCOUNT does not always reflect the number of rows affected in the table that the trigger is attached to.
From BOL:
Returns the number of rows affected by the last statement.
Trust me on this as I have had this experience!
Hi John,
Good point. Thanks for the warning.
I had completely forgotten about this because I never declare more than one trigger for the same action. Frankly, I never really understood why people want to have multiple triggers for an action; I always code all checks and changes required in a single trigger.
(And if you do have a single trigger, then you can safely check @@ROWCOUNT as the first statement in the trigger, as the last statement will THEN always be the statement that caused the trigger to fire.