SQL Server递归触发器意外触发深度限制的排查与优化

一、空Inserted触发的原因 #

• SQL Server的AFTER UPDATE触发器在执行UPDATE语句时，即使没有任何行被实际修改（比如SET的字段值与原值完全相同），只要语句中指定了更新该字段，UPDATE(ParentPermissionId)就会返回true，触发器会被触发。
• 你原来的第二个UPDATE语句是将子行的ParentPermissionId设为原值（parent.PermissionId就是子行当前的ParentPermissionId），属于无实际变更的更新。当没有匹配的子行时，Inserted集合就会为空，但触发器仍会因UPDATE(ParentPermissionId)为真而执行，导致空触发。
• 开启RECURSIVE_TRIGGERS后，这种无实际变更的更新会反复触发触发器，形成无效递归，直到达到SQL Server默认的32层嵌套限制。

二、递归超限的原因 #

• 原触发器依赖“更新子行ParentPermissionId为原值”来触发子行的触发器更新Ancestry，这种逻辑会导致无意义的递归调用：即使子行的ParentPermissionId没有变化，触发器仍会被触发，每触发一次就增加一层嵌套，直到达到32层限制。
• 空触发的情况会进一步加剧这个问题，因为无效的递归调用会持续消耗嵌套层级。

三、更优解决方案：直接批量更新后代节点 #

不需要依赖触发器递归，而是用CTE递归查询所有受影响节点的后代，一次性批量更新它们的Ancestry，避免无效触发和嵌套超限。修改后的触发器代码如下：

ALTER TRIGGER [Permissions_OnParentPermissionId_Change]
ON [Permissions] 
AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    -- 跳过无实际数据变更的情况
    IF NOT EXISTS(SELECT 1 FROM INSERTED) OR NOT UPDATE(ParentPermissionId)
        RETURN;

    -- 1. 更新当前变更行的Ancestry
    UPDATE p
    SET p.Ancestry = CASE 
                        WHEN i.ParentPermissionId IS NULL THEN NULL 
                        ELSE ISNULL(parent.Ancestry, '~') + CAST(parent.PermissionId AS NVARCHAR(10)) + '~' 
                    END
    FROM [Permissions] p
    INNER JOIN Inserted i ON p.PermissionId = i.PermissionId
    LEFT JOIN [Permissions] parent ON i.ParentPermissionId = parent.PermissionId;

    -- 2. 递归查询所有后代节点，批量更新Ancestry
    WITH RecursiveChildren AS (
        -- 初始节点：当前变更行的直接子节点
        SELECT 
            child.PermissionId,
            ISNULL(p.Ancestry, '~') + CAST(p.PermissionId AS NVARCHAR(10)) + '~' AS NewAncestry
        FROM [Permissions] child
        INNER JOIN Inserted p ON child.ParentPermissionId = p.PermissionId
        UNION ALL
        -- 递归获取所有深层后代节点
        SELECT 
            grandchild.PermissionId,
            rc.NewAncestry + CAST(grandchild.ParentPermissionId AS NVARCHAR(10)) + '~' AS NewAncestry
        FROM [Permissions] grandchild
        INNER JOIN RecursiveChildren rc ON grandchild.ParentPermissionId = rc.PermissionId
    )
    UPDATE p
    SET p.Ancestry = rc.NewAncestry
    FROM [Permissions] p
    INNER JOIN RecursiveChildren rc ON p.PermissionId = rc.PermissionId;
END

方案优势 #

• 避免无效递归：直接通过CTE获取所有后代节点，一次性完成更新，不需要触发子行的触发器，彻底解决嵌套超限问题。
• 效率更高：批量更新减少了触发器的多次调用，降低数据库开销。
• 逻辑清晰：直接维护Ancestry字段，不需要通过修改ParentPermissionId间接触发，避免无意义的更新操作。

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