.NET BulkCopy Only Firing Trigger On One Insert

Question

I perform a trigger on each insert on a table. I am using bulkcopy to do a bulk insert into this table. I read FireTriggers will fire my trigger on the table, but it only does it one time, not for each insert. The trigger takes some data from the inserted item, and inserts it into another table. I only see one row inserted in the other table, and not more then one. Am I missing an option?

var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default | SqlBulkCopyOptions.FireTriggers, transaction);

You'll probably have to post the code to your trigger, but I'm guessing it's not coded properly to work with multiple rows being inserted at once. — CodeNaked, Nov 07 '13 at 19:02

score 6 · Answer 1 · answered Nov 07 '13 at 19:02

"For each insert", or for each "row"? Sounds like you are expecting to see the trigger fire for each row.

This is a common misconception about triggers -- that they fire once per row. They don't. They fire once per change. There is a virtual table called "inserted" inside the body of the trigger which contains all of the new rows inserted. It sounds very much like the trigger in your database is only reading one row from this table, not the whole thing. This, sadly, is a common design flaw in the way a lot of people implement triggers in SQL Server databases.

The only way to work around this is to go row by row, which means you can't bulk copy -- you'll have to insert one row at a time. Better yet, fix the issue in the trigger code -- it's a huge bug and who knows what it's doing to the data integrity of your database? — Dave Markle, Nov 07 '13 at 19:07

Mike Flynn · Accepted Answer · 2013-11-09T19:04:19.367

0

I was already assigning a bulk group id to all the items. I accomplished this with the following:

1. If item has bulk group id
2. If item is being inserted, not updated
3. Do a select into from a selection of items based on the bulk group id

Trigger

    IF(@BulkGroupInsertId IS NULL OR EXISTS (SELECT * FROM DELETED))
    BEGIN
        -- Do Single Insert
    END
    ELSE
    BEGIN

    -- Bulk Insert

        INSERT INTO TeamSubscription (DivisionTeamId, PhoneNumber, DateCreated)
        SELECT tc.TeamId, p.MobilePhone, GETDATE()
        FROM
            -- Commented Out
        WHERE 
            -- Commented Out
        GROUP BY
            -- Commented Out

    END

edited Nov 09 '13 at 19:04

answered Nov 08 '13 at 04:31

Mike Flynn

22,342
54
182
341

If you honestly have a trigger which is exhibiting this behavior, I beseech you on behalf of everyone who will work on your project after you to get this bug fixed. – Dave Markle Nov 08 '13 at 14:31
I dont understand your comment. – Mike Flynn Nov 09 '13 at 18:51

score -1 · Answer 3 · answered Sep 15 '15 at 13:27

-1

Add this SQLBulkCopyOptions.FireTriggers while creating SQLBulkCopy Object.

answered Sep 15 '15 at 13:27

Jaydeep Shil

1,894
22
21

.NET BulkCopy Only Firing Trigger On One Insert

3 Answers3