删除自引用表的级联

Question

我有一个自引用的注释表。我试图写删除级联，但它需要一些例外

在表"注释"上引入外键约束"FK_Comments_Comments"可能会导致循环或多个级联路径。指定"删除时不执行任何操作"或"更新时不执行任何操作"，或修改其他外键约束。

然后尝试编写触发器，但它再次出现异常

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    FOR DELETE
AS
    DELETE FROM Comments
    WHERE ParentId =(SELECT deleted.id FROM deleted)

无法删除包含子项的行

如何对我的自引用表进行删除级联？

Answer 1

假设您保留了FOREIGN KEY约束，则无法在FOR DELETE触发器中修复问题。 FOR触发器(也称为AFTER触发器(在活动发生后触发。外键将防止删除具有引用的行。外键检查在删除之前进行。

您需要的是一个INSTEAD OF触发器。您还需要记住，您当前的触发器仅尝试处理一个"级别"的引用。(因此，如果第 3

行引用第 2 行，第 2 行引用第 1 行，而您删除第 1 行，则触发器仅尝试删除第 2 行(

所以，像这样：

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    INSTEAD OF DELETE
AS
    ;WITH IDs as (
       select id from deleted
       union all
       select c.id
       from Comments c
              inner join
            IDs i
              on
                 c.ParentID = i.id
    )
    DELETE FROM Comments
    WHERE id in (select id from IDs);

如果存在其他(非自引用(级联外键约束，则它们都必须替换为此触发器中的操作。在这种情况下，我建议引入一个表变量来保存最终将从Comments表中删除的所有 ID 的列表：

CREATE TRIGGER [dbo].[T_comment_Trigger]
    ON [dbo].[Comments]
    INSTEAD OF DELETE
AS
    declare @deletions table (ID varchar(7) not null);
    ;WITH IDs as (
       select id from deleted
       union all
       select c.id
       from Comments c
              inner join
            IDs i
              on
                 c.ParentID = i.id
    )
    insert into @deletions(ID)
    select ID from IDs
    -- Delete from FK referenced table
    DELETE FROM OtherTable
    WHERE CommentID in (select ID from @deletions)
    --This delete comes last
    DELETE FROM Comments
    WHERE id in (select ID from @deletions);