Sql Server触发器执行次数为变更行数平方的问题如何解决

问题根因 #

行数平方增长的核心原因是UPDATE分支的关联逻辑错误：
批量更新时，inserted和deleted两张系统表均存储了全量变更行记录，原代码使用逗号分隔两张表，等价于无关联条件的笛卡尔积连接。若两张表各有N行，会生成N*N行结果集，因此出现重复插入问题。该问题和触发器嵌套层级无关，因此TRIGGER_NESTLEVEL()判断无效。

修复后触发器代码 #

CREATE OR ALTER TRIGGER [dbo].[Trigger]
ON [dbo].[Table]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回影响行数信息引发上层逻辑异常

    IF UPDATE(Name)
        -- UPDATE场景
        IF EXISTS(SELECT Name FROM inserted) AND EXISTS(SELECT Name FROM deleted)
            INSERT INTO TriggerTable (Id, ColumnName, OldValue, NewValue)
                SELECT i.Id, 'Name', d.Name, i.Name
                FROM deleted d
                INNER JOIN inserted i ON d.Id = i.Id -- 核心修复：按主键匹配同一行的新旧记录
                WHERE d.Name <> i.Name OR (d.Name IS NULL AND i.Name IS NOT NULL) OR (d.Name IS NOT NULL AND i.Name IS NULL)
        -- INSERT场景
        ELSE IF EXISTS(SELECT Name FROM inserted)
            INSERT INTO  TriggerTable (Id, ColumnName, NewValue)
                SELECT i.Id, 'Name', i.Name
                FROM inserted i
        -- DELETE场景
        ELSE IF EXISTS(SELECT Name FROM deleted)
            INSERT INTO  TriggerTable (Id, ColumnName, OldValue)
                SELECT d.Id, 'Name', d.Name
                FROM deleted d
END

额外优化建议 #

• 新增的SET NOCOUNT ON是触发器开发的通用规范，可避免触发器执行返回的行数影响上层应用的DML操作判断
• 如果业务逻辑允许将空字符串和NULL视为等价，可将Name字段不等的判断简化为WHERE ISNULL(d.Name, '') <> ISNULL(i.Name, '')，简化代码逻辑
• 后续若需要监听更多字段变更，可使用UNION ALL拼接不同字段的查询结果，无需重复编写多组IF分支，减少代码冗余

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