Trigger is fire event if no row is inserted

Question

Trigger is fire event if no row is inserted

dubem1-878067

Ten Centuries

Points: 1044
More actions
November 30, 2008 at 10:19 am

#194512

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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

...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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?

dubem1-878067 Ten Centuries Points: 1044 More actions · Answer 5

I don't , the query I use is the one stated in my initial post.

Thanks to Lynn and Gail for the tips

Martin

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

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]

dubem1-878067 Ten Centuries Points: 1044 More actions · Answer 8

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Put this at the beginning of your trigger...

IF @@ROWCOUNT = 0 RETURN

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

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]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)