Deleting rows recursively in a self-referencing table using a CTE. How does the process take place?

Viewed 1352

I'm working on a side project, and in order to delete a row and all its descendants in a self-referencing table, I'm using a recursive CTE like this inside a trigger:

CREATE TRIGGER dbo.tr_Comment_Delete
    ON dbo.Comment INSTEAD OF DELETE
AS
    ;WITH IDs AS (
       SELECT id FROM DELETED
       UNION ALL
       SELECT c.id
       FROM Comment AS c INNER JOIN IDs AS i 
        ON c.parent_comment_id = i.id
    )
    DELETE FROM Comment
    WHERE id IN (SELECT id FROM IDs);
GO

This is the self-referencing table

enter image description here

Although I have this code working as expected, it is one of those cases in which you do something, but you're not quite sure how it works.

To be more precise, what I'd like to know is how it is that by using this recursive CTE(IDs) I'm able to avoid referential integrity errors when I try to delete a comment that has child comments?

What is it the process/order in which the comments are deleted?

Take this hierarchy of comments as an example:

3-> 8-> 13 

Here the comment of id 3 is the root comment. Comment 8 is a reply to comment 3,just like comment 13 is a reply to comment 8.

How does the deletion process actually take place?

P.S. I tried adding a table in which I inserted the Ids as they were calculated. Unfortunately I can't make sense of it. These are the results of such table:

id  ins-date
3   2017-09-12 11:48:38.037
8   2017-09-12 11:48:38.037
13  2017-09-12 11:48:38.037
13  2017-09-12 11:48:38.037
8   2017-09-12 11:48:38.037
13  2017-09-12 11:48:38.037
1 Answers
Related