JohnG (9/5/2008)
Only if you have one and only one trigger in the entire database!The trigger that was fired and thus sets @@ROWCOUNT to zero could be some DML operation in a trigger on another table. Note that @@ROWCOUNT is global and not scoped to the DML on the table. It is reset on DML within a trigger.
Hi John,
If a DML operation that causes a trigger to fire is started in a trigger, the flow of that trigger is interrupted and the other trigger gets executed first. Once that is finished, the first resumes. So when the second trigger starts, the @@ROWCOUNT will be equal to the number of rows affected by the DML operation in the first trigger that caused the second to fire, as expected.
I know this very well, because I have been heavily involved in maintaining a database that had lots of nested (and even recursive) trigger executions.
In my case I had a parent->child relationship (e.g., Master->Detail) that required a trigger on the parent table. The deletions from the child table were done via a CASCADE delete which then fired the required trigger on the child table.
So the trigger fired, it done some things, the last of which affected zero rows, then the trigger on the child table fired. Since the last DML operation in the prior trigger resulted in zero rows affected, the 2nd trigger didn't do anything as it erroneously was testing @@ROWCOUNT.
This was a bitch to track down as to why the trigger didn't work properly all of the time.
I repeat, DO NOT test @@ROWCOUNT at the beginning of a trigger. Properly check the number of rows in the related trigger tables.
Aaah, cascading deletes/updates. That is indeed a very good point. Yes, if you have those, you should indeed avoid testing @@ROWCOUNT, since the order in which the triggers fire is undefined.
So I guess I should back down and reuce my statement to "@@ROWCOUNT is safe if used as the first line of code in the trigger, if there is no more than one trigger for the operation, and if the table the trigger is defined upon is not involved on either side of a foreign key with cascading updates or deletes". (But in those cases, @@ROWCOUNT is better, because it's slightly faster than checking for existance of a row in the trigger tables, and potentially lots faster than counting those rows).