Lynn Pettis (9/5/2008)

---

I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.

Lynn Pettis (9/5/2008)

---

I must not fully understand INSERT triggers, but I was always under the impression that if nothing was inserted, an insert trigger WON'T fire. I have never had to write an insert trigger to handle a zero row insert; multi-row inserts, yes. I guess this is something I am going to have to investigate.

IF @@ROWCOUNT = 0 RETURN;

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.

JohnG (9/5/2008)

---

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!

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.

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.

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.

icocks (9/5/2008)

---

A further thing to bear in mind - if there's an Insert trigger on the table, then it will be fired in response to option 2, but not option 1. Depending on what it does (& how smart you've been in limiting the processing for a zero row insert) then there may be a further performance hit and/or lock escalation to take into account. Of course you may have good reasons for wanting it to fire for even an unsuccessful insert attempt.

In general I prefer to anti-join the value list back to the insert table since that's much more easily expandable for multi-row inserts & compound keys.

My opinion is that there's rarely a truly right or wrong answer to this kind of question - the real skill lies in understanding the strengths & weaknesses of each approach & then choosing the appropriate option for the system it's being applied to.