While use SqlBulkCopy trigger not triggered

Question

While use SqlBulkCopy trigger not triggered

thomasemp

Old Hand

Points: 308
More actions
September 26, 2011 at 4:21 pm

#245420

I have created a trigger for a particular table.While i insert a record the trigger is working perfectly but while i use SqlBulkCopy to upload records from excel to database the trigger is not working what should i do.
this is my trigger
ALTER TRIGGER [dbo].[CreateDummyforOthers] ON [dbo].[StudentPersonal]
AFTER INSERT
AS
DECLARE @StudentID as bigint
SET @StudentID=( SELECT @@IDENTITY)
INSERT INTO dbo.StudentAcademic (StudentID) Values(@StudentID)
INSERT INTO dbo.StudentOldAcademic (StudentID) Values(@StudentID)
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

that is by design. all bulk copying, whether using bcp.exe , BULK INSERT or via a SQLBulkCopy, triggers are not executed by default.

there is a flag you can set in all three of those methods, to trip the triggers, but you have to make the conscience decision to do so.

Dim myConn As New SqlConnection(Me.ConnectionString)

myConn.Open()

Using myBulkCopy As New SqlBulkCopy(myConn, SqlBulkCopyOptions.FireTriggers)

myBulkCopy.DestinationTableName = "[" & DestinationTable & "]"

myBulkCopy.WriteToServer(dt)

End Using

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Suresh B. SSC-Insane Points: 22986 More actions · Answer 2

By default BULK INSERT does not fire triggers. You have to use FIRE_TRIGGERS option.

Another work around is after Bulk Copy, you can easily insert the data to other tables as separate step.

Lowell SSC Guru Points: 323504 More actions · Answer 3

thomasemp (9/26/2011)
I have created a trigger for a particular table.While i insert a record the trigger is working perfectly but while i use SqlBulkCopy to upload records from excel to database the trigger is not working what should i do.
this is my trigger
ALTER TRIGGER [dbo].[CreateDummyforOthers] ON [dbo].[StudentPersonal]
AFTER INSERT
AS
DECLARE @StudentID as bigint
SET @StudentID=( SELECT @@IDENTITY)
INSERT INTO dbo.StudentAcademic (StudentID) Values(@StudentID)
INSERT INTO dbo.StudentOldAcademic (StudentID) Values(@StudentID)
Thanks.

also, your trigger will not work correctly when you run it with a bulk insert that puts in multiple rows...your trigger is designed for single row inserts.

you need to redesign it to handle multiple row insertions and use the INSERTED table within the trigger instead:

ALTER TRIGGER [dbo].[CreateDummyforOthers] ON [dbo].[StudentPersonal]

AFTER INSERT

AS

INSERT INTO dbo.StudentAcademic (StudentID)

SELECT StudentID FROM INSERTED

INSERT INTO dbo.StudentOldAcademic (StudentID)

SELECT StudentID FROM INSERTED

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

thomasemp Old Hand Points: 308 More actions · Answer 4

thomasemp

Old Hand

Points: 308

September 27, 2011 at 4:25 am

#1387023

Thanks Lowell.

It is working perfectly

Thanks lot.

pat 98396 Valued Member Points: 55 More actions · Answer 5

By default, SqlBulkCopy does not honor triggers or foreign key constraints. You should always include the options SqlBulkCopyOptions.FireTriggers and SqlBulkCopyOptions.CheckConstraints. (I consider this a flat out bug that the default behavior of the method is NOT to honor database consistency.)

Interestingly, it appears the way it does turn off the trigger functionality is by an Alter Table statement....which many of your users probably don't have permissions for. So instead of honoring the database consistency, it gets an exception. Lovely.

Similarly with foreign keys. If you use SqlBulkCopy without setting SqlBulkCopyOptions.CheckConstraints it simply leaves your foreign keys untrusted when it is done.

Can you tell I am frustrated with this tool?