On delete cascade for self-referencing table

Question

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?

It would really help if you *paid attention to* the error messages and *copied their text into the question*, rather than just saying "it take exception". — Damien_The_Unbeliever, Feb 14 '17 at 14:20
Your trigger has a major problem. It assumes there will only ever be a single row deleted. You need to use a join for this type of thing. — Sean Lange, Feb 14 '17 at 14:24

score 10 · Accepted Answer · edited Jan 12 '22 at 09:23

10

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

edited Jan 12 '22 at 09:23

Alex

4,885
3
19
39

answered Feb 14 '17 at 14:27

Damien_The_Unbeliever

234,701
27
340
448

an error : **Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'T_comment_Trigger' on table 'dbo.Comments'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.** occurs – Mike Feb 14 '17 at 14:31
@Mike - you need to remove *all* cascading foreign keys and implement all of the cascades manually inside this trigger. (You can still have the FKs, just not the cascade option) – Damien_The_Unbeliever Feb 14 '17 at 14:32
**My table has `on cascade delete` for other foreign keys** – Mike Feb 14 '17 at 14:33
@Mike - yes, and you have to give that up. I've added an example of how you'd manually implement the cascade inside the same trigger. – Damien_The_Unbeliever Feb 14 '17 at 14:37
is there any way except this way? – Mike Feb 14 '17 at 14:44
Hi @Damien_The_Unbeliever I came up with a [solution almost identical to the trigger you've shown here](https://stackoverflow.com/questions/46185846/deleting-rows-recursively-in-a-self-referencing-table-using-a-cte-how-does-the), the problem is that I don't get is why if I try to delete a comment, which have children, without the trigger I would get this error: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint FK_Comment_Comment". The conflict occurred in database "X", table "dbo.Comment", column 'parent_comment_id'. – eddy Sep 14 '17 at 07:32
But if I do it inside the trigger no error is thrown. Let's say I have this hierarchy of comments: `3-> 8-> 13`, If I'm not mistaken, the first time a try to delete the comment of id 3, the CTE inside the trigger should return these ids: `3,8,13`, right? If so, the statement at the end of the trigger should look like this : `DELETE FROM Comment WHERE id IN (3,8,13)`. Why trying to execute this statement does not throw any referential integrity error? – eddy Sep 14 '17 at 07:38

On delete cascade for self-referencing table

1 Answers1

Linked