Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS

Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS

Post by thin » Thu, 10 Dec 2009 09:16:41


I have an insert trigger that works just fine when I insert one row
When I perfrom a bulk insert, the trigger only seems to work on the last row
in the input file.
What should the trigger be doing to process each row of the inserted data?

Cheers

Bob
 
 
 

Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS

Post by Michael Co » Thu, 10 Dec 2009 09:37:27

What's your trigger code look like?

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
( http://www.yqcomputer.com/ )
----------------

 
 
 

Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS

Post by Erland Som » Thu, 10 Dec 2009 17:11:37

thing ( XXXX@XXXXX.COM ) writes:

Sounds like you have coded trigger like:

SELECT @var1 = col1, @var2 = col2 .. FROM inserted

Since triggers fires once per statement, a trigger written like this will
only handle one row when you do multirow inserts.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS

Post by thin » Fri, 11 Dec 2009 09:10:50

Thanks Erland,

I put in a cursor and a loop and now it works as expected

Bob
 
 
 

Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS

Post by Erland Som » Sat, 12 Dec 2009 07:42:06

thing ( XXXX@XXXXX.COM ) writes:

I don't know what you trigger does, but you should probably have found
a set-based solution for better performance.

--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Links for SQL Server Books Online:
SQL 2008: http://www.yqcomputer.com/
SQL 2005: http://www.yqcomputer.com/
SQL 2000: http://www.yqcomputer.com/