Trigger not working in BulkCopy in Sql Server

c# sqlbulkcopy sql-server triggers

Question

I have an application through which I'm inserting bulk records into a table using SqlBulkCopy. I have a trigger which inserts the records added into the First table into Another table.

When I'm inserting i have written SqlBulkCopyOptions.FireTriggers in C#, but its not working with Sql Server 2005. When worked with SqlServer2008 its working fine.

In Sql server 2005, Whenever the records are inserted into the First Table only one record from the INSERTED rows appear in the second table.

Can I manage this using Sql Server alone?

Expert Answer

One option here would be to bulk-copy into a staging table (a second, simple table - not part of the main data model; usually no indexing / constraints / triggers / etc), and then issue a SELECT-based INSERT to get the data from the staging table into the main transactional table. This allows you to get the bandwidth advantages of SqlBulkCopy, but still have full SQL-Server functionality for the final INSERT.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow