关于自引用表的删除级联 [英] On delete cascade for self-referencing table

问题描述

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

<块引用>

在表Comments"上引入 FOREIGN KEY 约束FK_Comments_Comments"可能会导致循环或多个级联路径.指定 ON DELETE NO ACTION 或 ON UPDATE NO ACTION，或修改其他 FOREIGN KEY 约束.

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

创建触发器 [dbo].[T_comment_Trigger]ON [dbo].[评论]删除作为从评论中删除WHERE ParentId =(SELECT deleted.id FROM deleted)

<块引用>

无法删除有子行的行

如何删除自引用表的级联删除?

解决方案

假设您保留了 FOREIGN KEY 约束，则无法在 FOR DELETE触发器.FOR 触发器(也称为 AFTER 触发器)在活动发生后触发.如果有引用，外键将防止删除一行.外键检查发生在删除之前.

您需要的是一个 INSTEAD OF 触发器.您还需要记住，您当前的触发器仅尝试处理一个级别"的引用.(因此，如果第 3 行引用第 2 行，第 2 行引用第 1 行，而您删除第 1 行，则触发器仅尝试删除第 2 行)

所以，例如:

创建触发器 [dbo].[T_comment_Trigger]ON [dbo].[评论]而不是删除作为;ID 为 (从删除中选择 id联合所有选择 c.id来自评论 c内部联接身份证号在c.ParentID = i.id)从评论中删除WHERE id in(从 ID 中选择 id)；

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

创建触发器 [dbo].[T_comment_Trigger]ON [dbo].[评论]而不是删除作为声明@deletions 表(ID varchar(7) 不为空)；;ID 为 (从删除中选择 id联合所有选择 c.id来自评论 c内部联接身份证号在c.ParentID = i.id)插入@deletions(ID)从 ID 中选择 ID从其他表中删除WHERE CommentID in(从@deletions 中选择 ID)--此删除在最后从评论中删除WHERE id in(从@deletions 中选择 ID)；

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.

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?

解决方案

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.

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)

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:

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

这篇关于关于自引用表的删除级联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！