对于自引用表的删除级联

I have a comment table that is self-referencing. I tried to write on delete cascade but it take some 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.

在表'Comments'上引入外键约束'FK_Comments_Comments'可能会导致循环或多个级联路径。在删除无操作或更新无操作时指定，或修改其他外键约束。

and then try to write a trigger but it take exception again

然后尝试写一个触发器，但它又有例外

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

不能删除有子元素的行

how can I do on delete cascade for my self-referencing table?

我如何为我的自引用表执行删除级联?

1 个解决方案

#1

Assuming you're keeping your FOREIGN KEY constraint in place, you cannot fix the issue in a FOR DELETE trigger. FOR triggers (also known as AFTER triggers) fire after the activity has taken place. And a foreign key will prevent a row from being deleted if it has references. Foreign key checks occur before deletion.

假设您保留了外键约束，则无法在FOR DELETE触发器中修复这个问题。触发器(也称为AFTER触发器)在活动发生后触发。如果有引用的话，外键可以防止一行被删除。在删除之前进行外键检查。

What you need is an INSTEAD OF trigger. You also need to bear in mind that your current trigger only tried to deal with one "level" of referencing. (So, if row 3 references row 2 and row 2 references row 1, and you delete row 1, your trigger only tried to remove row 2)

你需要的是一个而不是触发器。您还需要记住，当前触发器只尝试处理一个“级别”的引用。(因此，如果第3行引用第2行和第2行引用第1行，删除第1行，您的触发器只尝试删除第2行)

So, something like:

所以,类似:

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);

If there are other (non-self-referencing) cascading foreign key constraints, they all have to be replaced by actions in this trigger. In such a case, I'd recommend introducing a table variable to hold the list of all IDs that will eventually be deleted from the Comments table:

如果有其他(非自引用)级联外键约束，它们都必须被触发器中的动作所取代。在这种情况下，我建议引入一个表变量来保存最终将从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 OtherTable
    WHERE CommentID in (select ID from @deletions)

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