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);
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
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
"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.