SQL Azure批量插入时触发器未全量触发的问题及优化问询

我来帮你搞定这个批量插入触发器的问题！首先得明确原触发器的核心问题：当批量插入4万+条记录时，INSERTED表会包含所有新插入的行，但你直接用单个变量从INSERTED取值，这种情况下变量只会获取到最后一行的数据，所以自然只触发了1-2条记录的处理。

针对你的三个需求，我给你修改后的触发器代码，同时解释关键改动点：

关键改动说明 #

• 获取DISTINCT列值并排序：从INSERTED表中提取去重后的ITEMNUMBER、PRICEAPPLICABLEFROMDATE、PARTYCODETYPE，并按业务需求排序（示例按ITEMNUMBER和生效日期排序）
• 仅插入指定列到临时表：不再用SELECT *，而是明确指定需要的列，避免加载不必要的数据，提升批量处理性能
• 批量处理支持：用游标遍历去重后的每条记录，逐个调用存储过程，确保批量插入时所有符合要求的distinct记录都被处理

修改后的触发器代码 #

/****** Object: Trigger [dbo].[PriceStagingInsertTrigger] Script Date: 29/09/2020 13:46:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[PriceStagingInsertTrigger] on [dbo].[SalesPriceStaging]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回额外行数影响调用方

    -- 1. 创建临时表存储去重并排序后的指定列
    CREATE TABLE #DistinctRecords (
        ItemNumber NVARCHAR(20),
        ApplicableFromDate DATETIME,
        PartyCodeType INT
    );

    -- 插入去重、排序后的记录到临时表
    INSERT INTO #DistinctRecords (ItemNumber, ApplicableFromDate, PartyCodeType)
    SELECT DISTINCT
        ins.ITEMNUMBER,
        ins.PRICEAPPLICABLEFROMDATE,
        ins.PARTYCODETYPE
    FROM INSERTED ins
    ORDER BY ins.ITEMNUMBER, ins.PRICEAPPLICABLEFROMDATE; -- 可根据业务调整排序规则

    -- 2. 声明游标遍历去重后的记录
    DECLARE 
        @ItemNumber NVARCHAR(20), 
        @ApplicableFromDate DATETIME, 
        @PartyCodeType INT;

    DECLARE recordCursor CURSOR FOR
        SELECT ItemNumber, ApplicableFromDate, PartyCodeType
        FROM #DistinctRecords;

    OPEN recordCursor;
    FETCH NEXT FROM recordCursor INTO @ItemNumber, @ApplicableFromDate, @PartyCodeType;

    -- 3. 循环调用存储过程处理每条记录
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC dbo.spSalesPriceStaging @ItemNumber, @ApplicableFromDate, @PartyCodeType;
        
        FETCH NEXT FROM recordCursor INTO @ItemNumber, @ApplicableFromDate, @PartyCodeType;
    END

    -- 清理资源
    CLOSE recordCursor;
    DEALLOCATE recordCursor;
    DROP TABLE #DistinctRecords;

    PRINT 'Stored procedure spSalesPriceStaging executed for all distinct records and completed';
END
GO

额外优化建议 #

如果spSalesPriceStaging支持批量处理的话，建议改成用表值参数直接传入临时表的所有记录，这样可以避免游标循环带来的性能开销，对于4万+条记录来说会更高效；另外可以在触发器中添加TRY...CATCH错误处理块，避免触发器执行失败导致整个批量插入操作回滚。

内容的提问来源于stack exchange，提问作者Dinesh Tripathi