CODEWITHSUNDEEP
sqlsql-server
llerdal
llerdal

Reputation: 387

Modify SQL Trigger to work with BULK INSERT

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.

Upvotes: 4

Views: 21159

Answers (4)

Nayanesh Patel
Nayanesh Patel

Reputation: 1

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

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

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.

Upvotes: 9

trungbkdn
trungbkdn

Reputation: 31

Only need to edit BULK INSERT File below:

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

Upvotes: 3

Nibin C
Nibin C

Reputation: 1

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)

Upvotes: -1

Related Questions