Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Best Practice Expand / Collapse
Author
Message
Posted Friday, September 5, 2008 9:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:06 AM
Points: 47, Visits: 599
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.



Took me ages before I realised this was the case (I only found out thanks to some odd problems on a customer db). The same is true of Update/Delete triggers.

If a tree falls in a forest & no-one hears it, it may or may not make a sound. But it will cause a trigger to fire :)
Post #564677
Posted Friday, September 5, 2008 12:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 5,977, Visits: 8,237
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.



As others have already poitned out, the trigger stil fires even if no rows are affected. 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. I once have spent way too long staring at mysterious results until I realised that I had put this statement after the SET NOCOUNT ON, so I checked the @@ROWCOUNT from that SET statement (which is always 1).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #564839
Posted Friday, September 5, 2008 12:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:29 PM
Points: 23,298, Visits: 32,037
I would also guess it depends on what you are doing inside the trigger as well. As I stated earlier, when I did write triggers (not that I don't now when appropriate) I did n't do anything that would have issues (problems) if no records were inserted. Never had any anomilies in my databases that could be the result of a zero record insert.

Having learned this, however, is definately an asset for future activities.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #564847
Posted Friday, September 5, 2008 1:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:38 AM
Points: 1,182, Visits: 1,972
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!



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #564861
Posted Friday, September 5, 2008 2:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 5,977, Visits: 8,237
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #564912
Posted Friday, September 5, 2008 3:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 6:38 AM
Points: 1,182, Visits: 1,972
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.



(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Post #564923
Posted Friday, September 5, 2008 3:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 5,977, Visits: 8,237
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).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #564933
Posted Friday, September 5, 2008 5:41 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
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.
Post #564948
Posted Sunday, September 7, 2008 12:43 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
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?
Post #565102
Posted Sunday, September 7, 2008 7:20 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512
Great stuff here everyone. Thanks OP!!!!! :D
Post #565191
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse