September 5, 2008 at 3:18 pm
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.
September 5, 2008 at 3:59 pm
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).
September 5, 2008 at 5:41 pm
I always use IF EXISTS(select 1 from inserted) or deleted.
It allows not to run "insert" part of INSERT/UPDATE/DELETE trigger only when there are actually inserted rows.
_____________
Code for TallyGenerator
September 7, 2008 at 12:43 am
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.
Look on what you are choosing from:
1. A solution which may cause errors in production environment but easy on resources;
2. A solution which is error safe but potentially causes some overhead.
Which one wold you choose for your car?
Are those airbags an overhead?
Have you ever been in a situation when you really needed it?
Have any of you friends?
So when you choose a car - will you prefer the one without airbags?
Why?
_____________
Code for TallyGenerator
September 7, 2008 at 7:21 pm
Great stuff here everyone. Thanks OP!!!!! 😀
September 8, 2008 at 12:02 am
This is really useful information, thanks guys!
Best Regards,
Chris Büttner
September 8, 2008 at 3:35 pm
I looked at this as more of a which one performs better question rather than the nature of the operation hence I chose 1.
I guess the absence of a transactin in the first statement has pulled the wool over everyones eyes!
Very good question this one, tip top!
September 8, 2008 at 6:54 pm
Very well put on the tree falling in the woods comment, in my experience triggers appear to go by rows affected but also by statements in the execution i.e. if it reads the word "UPDATE" in a piece of code and there is an ON Update trigger on that table...f***en Bang.
September 8, 2008 at 7:03 pm
Sergiy (9/7/2008)
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.
Look on what you are choosing from:
1. A solution which may cause errors in production environment but easy on resources;
2. A solution which is error safe but potentially causes some overhead.
Which one wold you choose for your car?
Are those airbags an overhead?
Have you ever been in a situation when you really needed it?
Have any of you friends?
So when you choose a car - will you prefer the one without airbags?
Why?
Having walked away from an accident where I slammed my car into a ditch at 70 MPH, I want both seat belts (lap and shoulder) and air bags.
😎
September 8, 2008 at 11:10 pm
Excellent question...
September 10, 2008 at 6:51 am
Ric Sierra (9/4/2008)
I'm not completly agree with the answer, because depends of the context:If you are looking for the best execution plan the answer is #1
If you are looking for less deadlock the answer is #2
But if the target is INSERT a new record, both solutions works.
Isn't 'correctness' favoured first ?
i.e. you look to ensure that you achieve what was intended and then look to optimise (secondary). The correct thing in my mind would be option 2 as explained in the solution.
Let the definition of 'correctness' commence...
October 20, 2008 at 6:35 pm
good question followed by interesting discussion. 🙂
October 24, 2008 at 3:19 am
Good Question But not clear in the sense of Blocking the record.
December 13, 2012 at 6:48 am
good question
+3 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply