SQL Server 修改SQL触发器以使用BULK INSERT

xj3cbfub 于 2022-11-21 发布在其他

关注(0)|答案(3)|浏览(223)

我有一个SQL触发器没有触发，因为表中的记录是通过BULK INSERT插入的。我没有访问插入记录的代码的权限，因此我需要修改此触发器以处理BULK INSERT。以下是触发器：

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

有人能帮我修改这个触发器来处理BULK INSERT吗？

sql-server

来源：https://stackoverflow.com/questions/42681388/modify-sql-trigger-to-work-with-bulk-insert

3条答案

按热度按时间

mrphzbgm1#

除非您可以修改BULK INSERT叙述句，否则会卡住。依照预设，触发程序不会在大量插入期间执行。您必须在命令中使用FIRE_TRIGGER选项明确开启触发程序。

赞(0）回复(0）举报 2022-11-21

pwuypxnk2#

只需要编辑下面的BULK INSERT文件：

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

赞(0）回复(0）举报 2022-11-21

ie3xauqp3#

问题是它只选择了插入表的最后一行，我想如果你像这样更改查询，它会工作

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)

赞(0）回复(0）举报 2022-11-21

我来回答

SQL Server 修改SQL触发器以使用BULK INSERT

3条答案

相关问题

热门标签

最新问答