Modify SQL Trigger to work with BULK INSERT

Question

I have a SQL Trigger that doesn't fire because the records in the table are inserted through a BULK INSERT. I do not have access to the code that inserts the records so I need to modify this trigger to handle the BULK INSERT. This is the trigger:

    USE [testdata]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Trigger_test] ON [dbo].[test]
    AFTER INSERT , UPDATE
AS 

BEGIN

    DECLARE @BatchId int, @Ethanol decimal(18,6), @Glucose decimal(18,6), @SampleAge varchar(50);

    SELECT @BatchId = CONVERT(int,bd.[BatchId]),
           @Ethanol = CONVERT(decimal(18,2),[Ethanol]), 
           @Glucose= CONVERT(decimal(18,2),[Glucose]), 
           @SampleAge = bd.SampleCode
    from INSERTED bd

    update [dbo].[DeSchedule] 
    SET 
            [Ethanol] = @Ethanol,
            [Glucose] = @Glucose,
            [SampleCompleted] = 1 
    WHERE [BatchID] = @BatchId AND [SampleAge] = @SampleAge
END

Can anyone help me in modifying this trigger to handle the BULK INSERT.

Your trigger has a MAJOR flaw. it assumes that the inserted table will only ever have a single row. Triggers are fired per operation not per row. And BULK INSERT means you are almost always going to have multiple rows. You need set based logic, not scalar logic. — Sean Lange, Mar 08 '17 at 20:36
http://stackoverflow.com/questions/19002947/fire-trigger-for-every-inserted-row-using-sqlbulkcopy — William Xifaras, Mar 08 '17 at 20:39
Gaaaaaaaaaaaaaaa. There could be/probably is more than one row in INSERTED. (as previously stated). Please do not write a "theres only one row" type of trigger. Gaaaaaaaaaaaaaaa. — granadaCoder, Mar 09 '17 at 02:06

score 7 · Answer 1 · edited Nov 17 '22 at 14:44

7

Unless you can modify the BULK INSERT statement you are stuck. By default triggers do NOT run during a bulk insert. You must explicitly turn them on in the command with the FIRE_TRIGGER option.

edited Nov 17 '22 at 14:44

Ian Kemp

28,293
19
112
138

answered Mar 08 '17 at 20:35

Sean Lange

33,028
3
25
40

score 3 · Answer 2 · edited Jul 17 '21 at 18:58

3

Only need to edit BULK INSERT File below:

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
         FIELDTERMINATOR =' |'
         , ROWTERMINATOR = ':\n'
         , FIRE_TRIGGERS
      );

edited Jul 17 '21 at 18:58

ADITYA PAWAR

198
2
13

answered Oct 25 '19 at 06:12

trungbkdn

31
2

This was exactly what I needed. – Brad Nov 01 '19 at 17:32

score 0 · Answer 3 · edited Jan 03 '23 at 10:04

USE [testdata]
GO
SET ANSI_NULLS ON;

GO
SET QUOTED_IDENTIFIER ON;

GO

ALTER TRIGGER [dbo].[Trigger_test] ON [dbo].[test]
    AFTER INSERT , UPDATE
AS 

BEGIN

SET NOCOUNT ON;

    DECLARE @BatchId int, @Ethanol decimal(18,6), @Glucose decimal(18,6), @SampleAge varchar(50);

    Declare CurI cursor for 
    SELECT [BatchId],[Ethanol] ,[Glucose], SampleCode  from INSERTED

    Open CurI
        fetch next from CurI into @BatchId,@Ethanol,@Glucose, @SampleAge
        while @@fetch_status=0
        Begin
            update [dbo].[DeSchedule] 
            SET 
                    [Ethanol] = @Ethanol,
                    [Glucose] = @Glucose,
                    [SampleCompleted] = 1 
            WHERE [BatchID] = @BatchId AND [SampleAge] = @SampleAge
  
            Fetch next from CurI into @BatchId,@Ethanol,@Glucose, @SampleAge
         End

    Close CurI
    Deallocate CurI

    
END

Nibin C · Answer 4 · 2017-12-04T11:05:22.893

the problem is it selected only the last row of the inserted table ,i think if you change the query like this it would work

update [dbo].[DeSchedule] 
    SET 
            [Ethanol] =(select CONVERT(int,bd.[Ethanol]) from inserted bd),
            [Glucose] = (select CONVERT(decimal(18,2),[Glucose]) from inserted bd),
            [SampleCompleted] = 1 
    WHERE [BatchID] = (select CONVERT(int,bd.[BatchId]) from inserted bd) AND [SampleAge] = (select bd.SampleCode from inserted bd)

Modify SQL Trigger to work with BULK INSERT

4 Answers4