SQL Server触发器异常：SAP WebService插入时发票行合并金额错误

核心问题 #

在T_INVOICES表中，需合并同一发票下描述字段（对应XML字段2.2.1.4）相同的记录：手动合并逻辑正常，触发器在SSMS中执行单条/多条插入语句时工作正常，但通过SAP WebService批量插入时，合并后的金额仅取单条值（如预期3000实际为1000），推测是触发器批量处理逻辑或执行顺序问题。

可能原因 #

• 未处理批量插入的inserted表：SAP WebService大概率是批量提交数据，若触发器仅按单条记录逻辑处理，会遗漏批量数据中的同描述行，导致累加错误。
• 行级触发器的重复执行问题：如果是行级触发器，批量插入时每行触发一次，可能导致合并逻辑重复执行，覆盖正确的累加结果。
• 并发冲突：WebService插入时的并发场景下，触发器未锁定目标行，导致读取到不一致的中间数据。

解决方案 #

1. 重构触发器为语句级，适配批量插入 #

将触发器改为AFTER INSERT, UPDATE语句级触发器，先统计同发票同描述的总金额，再合并重复行：

CREATE TRIGGER trg_MergeInvoiceLines
ON T_INVOICES
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 统计需合并行的总金额，确定保留的主行
    WITH MergedLines AS (
        SELECT 
            InvoiceID, -- 发票唯一标识字段
            [Description], -- 对应XML字段2.2.1.4的描述字段
            SUM(Amount) AS TotalAmount,
            MIN(LineID) AS KeepLineID -- 保留最早/最小ID的行作为主行
        FROM T_INVOICES
        WHERE (InvoiceID, [Description]) IN (
            SELECT InvoiceID, [Description] FROM inserted
        )
        GROUP BY InvoiceID, [Description]
        HAVING COUNT(*) > 1
    )
    -- 更新主行金额为累加总额
    UPDATE t
    SET t.Amount = ml.TotalAmount
    FROM T_INVOICES t
    JOIN MergedLines ml ON t.InvoiceID = ml.InvoiceID AND t.LineID = ml.KeepLineID;

    -- 删除重复的子行
    DELETE t
    FROM T_INVOICES t
    JOIN MergedLines ml ON t.InvoiceID = ml.InvoiceID AND t.[Description] = ml.[Description]
    WHERE t.LineID <> ml.KeepLineID;
END
GO

2. 强制指定触发器执行顺序（SQL Server专属） #

若表上存在多个触发器，可通过sp_settriggerorder存储过程指定当前合并触发器的执行优先级，确保它在其他触发器之前运行：

-- 设置INSERT操作时该触发器第一个执行
EXEC sp_settriggerorder 
    @triggername = 'trg_MergeInvoiceLines',
    @order = 'First',
    @stmttype = 'INSERT';

-- 针对UPDATE操作也设置执行顺序
EXEC sp_settriggerorder 
    @triggername = 'trg_MergeInvoiceLines',
    @order = 'First',
    @stmttype = 'UPDATE';

3. 添加事务与行锁定，解决并发问题 #

在触发器中引入事务，并使用UPDLOCK+HOLDLOCK提示锁定目标行，避免WebService批量插入时的并发数据不一致：

CREATE TRIGGER trg_MergeInvoiceLines
ON T_INVOICES
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    BEGIN TRY
        WITH MergedLines AS (
            SELECT 
                InvoiceID,
                [Description],
                SUM(Amount) AS TotalAmount,
                MIN(LineID) AS KeepLineID
            FROM T_INVOICES WITH (UPDLOCK, HOLDLOCK) -- 锁定行直到事务结束
            WHERE (InvoiceID, [Description]) IN (
                SELECT InvoiceID, [Description] FROM inserted
            )
            GROUP BY InvoiceID, [Description]
            HAVING COUNT(*) > 1
        )
        UPDATE t
        SET t.Amount = ml.TotalAmount
        FROM T_INVOICES t
        JOIN MergedLines ml ON t.InvoiceID = ml.InvoiceID AND t.LineID = ml.KeepLineID;

        DELETE t
        FROM T_INVOICES t
        JOIN MergedLines ml ON t.InvoiceID = ml.InvoiceID AND t.[Description] = ml.[Description]
        WHERE t.LineID <> ml.KeepLineID;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- 抛出错误便于排查
    END CATCH
END
GO

验证建议 #

• 模拟SAP WebService的批量插入逻辑（一次性插入3条同描述的发票行），测试触发器是否正确累加金额。
• 在触发器中临时添加日志表记录，输出每次触发时inserted表的所有数据，确认WebService是否为批量提交。

内容的提问来源于stack exchange，提问作者stefano.pin88