sql - 关于自引用表的删除级联

Question

我有一个自引用的评论表。
我试图写在删除级联上，但它有一些异常(exception)

Introducing FOREIGN KEY constraint 'FK_Comments_Comments' on table 'Comments' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

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

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

couldn't delete rows that have children

如何为我的自引用表删除级联？

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 的列表。 table :

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 OtherTable
    WHERE CommentID in (select ID from @deletions)

    --This delete comes last
    DELETE FROM Comments
    WHERE id in (select ID from @deletions);