Unable to Run Trigger with SqlBulkCopy, Please Help

Question

Unable to Run Trigger with SqlBulkCopy, Please Help

Shubhajyoti Ghosh

SSC Veteran

Points: 203
More actions
June 25, 2011 at 12:56 pm

#143951

I have tried hard to work with SqlBulkCopy & Insert Trigger but -- the insert trigger on the table that SqlBulkCopy is sending data to is not running. If I insert with a regular INSERT query the trigger fires...but if I do the insert with SqlBulkCopy it does not.
I already tried and set
Option 1) SqlBulkCopyOptions.FireTriggers
Option 2) SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.CheckConstraints
using (SqlBulkCopy bcp = new SqlBulkCopy("ConnectionString", SqlBulkCopyOptions.FireTriggers))
{
bcp.DestinationTableName = "MediaDetails_Dump"; //Table Name
bcp.ColumnMappings.Add(0, 0);
bcp.ColumnMappings.Add(1, 1);
bcp.WriteToServer(DataTable);
//bcp.Close();
}
CREATE TRIGGER trgAfterInsert ON [MediaDetails_Dump]
FOR INSERT
AS
insert into MediaDetails
(MediaId, LanguageId
)
select MM.MediaId, 1
from inserted I
inner join MediaMst MM
on I.MediaTitle = MM.MediaTitle
PRINT 'AFTER INSERT trigger fired.'
GO
What is the wrong with my code. I unable to understand. Please help me

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

I would not use a trigger in this way. Avoid triggers where possible. Is it necessary here? Why not allow the bulk copy to complete and then call a stored procedure to load the new data into MediaDetails?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Shubhajyoti Ghosh SSC Veteran Points: 203 More actions · Answer 2

Your options is welcome. I did the job that way. But I just want to know why it is not working?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Shubhajyoti Ghosh (6/25/2011)
But I just want to know why it is not working?

Try setting the SqlBulkCopyOptions to FireTriggers in the SqlBulkCopy

constructor.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Shubhajyoti Ghosh SSC Veteran Points: 203 More actions · Answer 4

@Welsh Corgi

had u check my posted code. I already used SqlBulkCopyOptions.FireTriggers

Via Sql Profiler now I able to see Trigger is fired. But data is not inserted into destination table after bulk copy operation.

Shubhajyoti Ghosh SSC Veteran Points: 203 More actions · Answer 5

I made a blunder. Problem with Test data. Now every thing is solved. 🙂

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Are you joining on the correct table in your trigger?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Shubhajyoti Ghosh SSC Veteran Points: 203 More actions · Answer 7

Ya I join corrected table. but test with different data which is not available in Master Table . That is why join condition not satisfy. So Trigger fired but not insert data into table. 😉

Sorry my mistake. from yesterday onward I am just wasting my time for this blunder. What I say !!! 😛

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Now that you have determined the source of the problem, you should seriously consider avoiding a trigger unless absolutely necessary as was suggested.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/