November 30, 2008 at 10:19 am
Hello all.
it's my first post here. I hope someone will help me
I have a trigger fireing on inserting in table1
I have this query to duplicate some rows of table1, (the destination rows refer to another foreign key than the source rows)
INSERT INTO Table1(column1, columns2, columns 3, FK1)
SELECT column1, columns2, columns 3, @NewFK
FROM Table1
WHERE FK1= 999
Sometimes, the Select inside the Insert statement return 0 row, so there is no new Insert in table 1 but the trigger is fire.
It should not since no row as been inserted. How to avoid this?
If you need more informations please tell
Thank you
Martin
November 30, 2008 at 11:02 am
dubem1 (11/30/2008)
Hello all.it's my first post here. I hope someone will help me
I have a trigger fireing on inserting in table1
I have this query to duplicate some rows of table1, (the destination rows refer to another foreign key than the source rows)
INSERT INTO Table1(column1, columns2, columns 3, FK1)
SELECT column1, columns2, columns 3, @NewFK
FROM Table1
WHERE FK1= 999
Sometimes, the Select inside the Insert statement return 0 row, so there is no new Insert in table 1 but the trigger is fire.
It should not since no row as been inserted. How to avoid this?
If you need more informations please tell
Thank you
Martin
You aren't the first one to think that if no rows are inserted that the trigger isn't fired. Unfortunately, even if no rows are inserted, the insert trigger still fires as you have discovered.
What I would suggest, and others my have other ideas on this, is to run a quick count of the INSERTED table at the start of your insert trigger. If the row count is greater than zero, do your processing.
code snippet:
declare @inscnt int; -- INSERTED row count
select @inscnt = count(*) from INSERTED;
if @inscnt > 0
begin
trigger code -- trigger code goes here
end
...
November 30, 2008 at 11:19 am
Lynn Pettis (11/30/2008)
declare @inscnt int; -- INSERTED row count
select @inscnt = count(*) from INSERTED;
if @inscnt > 0
begin
trigger code -- trigger code goes here
end
...
Or, preferably, since the only thing of interest is whether or not there is a row, use EXISTS. Since it's not important whether there are 45 rows or 45 million, the exists is faster as it won't count potentially thousands or millions of rows just to see if there is at least one.
if EXISTS (SELECT 1 from inserted)
begin
trigger code -- trigger code goes here
end
...
The optimiser (on 2008, perhaps 2005) is smart enough to not count all of the rows and treat the query like EXISTS if the select is done within the if statement, but it's not smart enough not to count the rows if the count is put into a variable first.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2008 at 11:25 am
See, I knew someone would have a better solution. I know, here's my excuse, it's early and the family is decorating the Christmas tree and the fights are just beginning, so my brain wasn't fully engaged in finding the optimum solution. Yea, that works, that's my story, and I'm sticking to it.
November 30, 2008 at 11:43 am
I see the value in all those good tips... but since almost all my triggers do an inner join on the inserted and deleted tables... the fact that no rows are processed is of very little interest to me.
My real question here is... are you by any chance updating all the rows of the tables, or perhaps doing somedata manipulations inside a loop or something of the likes?
November 30, 2008 at 11:52 am
I don't , the query I use is the one stated in my initial post.
Thanks to Lynn and Gail for the tips
Martin
November 30, 2008 at 11:57 am
That code makes no sense to me... maybe I'm missing the point.
In any case, if you were using the inserted table in the trigger, you would have no problems if the insert statement inserts nothing (assuming I think I know what you want to do here!).
November 30, 2008 at 12:02 pm
The way to think of Triggers on SQL Server is that they are triggered by the modification statement and not by the actual modification of the individual rows.
This explains both why you only get one trigger event per statement, no matter how many rows are inserted, deleted or updated, and also why you still get a trigger event, even if no rows are actually inserted, updated or deleted. It's the statement that activates the trigger, not the rows.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 30, 2008 at 12:04 pm
no no
the query is in a stored procedure, it's used to duplicate some rows and affected id to a new foreign key.
The trigger is used to log the action in a transaction log.
Martin
November 30, 2008 at 12:10 pm
Lynn Pettis (11/30/2008)
Yea, that works, that's my story, and I'm sticking to it.
😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2008 at 8:14 pm
Put this at the beginning of your trigger...
IF @@ROWCOUNT = 0 RETURN
--Jeff Moden
November 30, 2008 at 9:33 pm
Jeff is that valid even in later triggers? Or will the earlier triggers actions overwrite it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2008 at 5:44 pm
Valid for all AFTER triggers on the same table regardless of how many there are... I don't have the test code to prove it, anymore, but someone could do the proof pretty easily.
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply