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.
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.
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.