SQL审计触发器更新基表时向审计表插入多条重复记录问题求助

问题原因 #

• SQL Server触发器为语句级触发，inserted与deleted表会存储本次操作涉及的所有变更行，你当前的触发器写法默认两个表中仅存在1行数据，仅适配单行操作场景，本身存在逻辑缺陷。
• 本次执行UPDATE语句后生成2条审计记录，常见触发场景有两类：
  1. 本次UPDATE语句匹配到了2行符合条件的记录，你的变量赋值逻辑仅会取第一行的字段值，若触发了递归执行或存在其他重复触发逻辑，就会生成多条值相同的审计记录。
  2. 该表上存在多个逻辑相同的AFTER INSERT/UPDATE触发器，执行UPDATE时多个触发器依次触发，分别向审计表插入了记录。

修复方案 #

将原有的单变量赋值+单行插入逻辑，改为基于集合的批量插入逻辑，兼容单行/多行操作场景，同时避免重复判断的逻辑漏洞。修复后的触发器代码如下：

CREATE OR ALTER TRIGGER dbo.SOP10100_TRDISAMT
ON dbo.SOP10100
AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    INSERT INTO SOP10100_TRDISAMT_AUDIT (
        soptype, sopnumbe, MSTRNUMB, DOCID, SUBTOTAL, MISCAMT, 
        TRDISAMT, TRDISAMT_B4, FRTAMNT, TAXAMNT, DOCAMNT, USERNAME, TheTime
    )
    SELECT 
        i.soptype,
        i.sopnumbe,
        i.mstrnumb,
        i.docid,
        i.subtotal,
        i.MISCAMNT,
        i.TRDISAMT,
        d.TRDISAMT,
        i.FRTAMNT,
        i.TAXAMNT,
        i.DOCAMNT,
        SUSER_SNAME(),
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d 
        ON i.SOPTYPE = d.SOPTYPE 
        AND i.SOPNUMBE = d.SOPNUMBE
    WHERE i.TRDISAMT <> 0;
END

如果修改触发器后仍然存在重复插入的问题，可执行以下语句排查额外原因：

1. 查询该表所有触发器，确认是否存在重复逻辑的触发器：

SELECT name FROM sys.triggers WHERE parent_id = OBJECT_ID('dbo.SOP10100')

2. 检查数据库递归触发配置，确认是否开启了递归触发：

SELECT is_recursive_triggers_on FROM sys.databases WHERE name = DB_NAME()

内容的提问来源于stack exchange，提问作者Bill Blue