Insert Trigger does not work on bulk insert; trigger is working with cursor

Question

On bulk insert, Insert trigger only works for the first record and does not work for all the other records but trigger works properly when records are inserted using cursor.

Insert trigger updates few columns of destination table. To insert bulk data I am using following script

INSERT INTO DestinationTable (Column1, Column2)
SELECT * FROM SourceTable

I get few columns in trigger of inserted record like the following script, and work on them to update columns of DestinationTable

SELECT @col1 = Column1, @col2 = Column2, FROM INSERTED

1. Why on bulk insert, trigger does not work?
2. Am I missing something or I have to use cursor?

I am using SQLServer 2005

EDIT

Trigger Code

http://stashbox.org/957108/InsertTrigger.sql

Thanks.

Answer 1

Well on cursor it is because each record is being inserted one at a time and not in a batch. So with bulk insert they insert in a batch. So the trigger fires once for the batch.

I think I read about a workaround one time that was pretty clean. Let me see if I can find it.

EDIT: You know when you said bulk operation I didn't even pay attention to the sql and assumed you were using bcp. But I do still remember a work around that I am going to look for.

EDIT2: Ok take a look at this article and see if it helps you out: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Answer 2

From the code you posted it looks like by bulk insert you just mean inserting multiple rows. Not this BULK INSERT?

The INSERTED pseudo table contains all the rows inserted by the statement. It is not a row level trigger. You would need to use a cursor for RBAR processing or, ideally, process it as a set. For example if you are Updating another table you could join onto the inserted table and update all the rows in one statement.