Deleting rows recursively in a self-regulation table using CTE. How is the process going?

Question

I am working on a side project, and to delete the row and all its descendants in the self-regulation table, I use a recursive CTE, as this happens inside the 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 a self-regulation table

Although this code works for me as expected, this is one of those cases when you do something, but you are not quite sure how it works.

To be more precise, what I would like to know is that using this recursive CTE (ID), can I avoid referential integrity errors when I try to delete a comment with child comments?

What is this process / order in which comments are deleted?

Take this comment hierarchy as an example:

3-> 8-> 13 

id 3 root. 8 - 3, , 13 - 8.

?

P.S. , , . , . :

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

Answer 1

, , .

:

CTE

, DELETE. SELECT .

, :

• SELECT recurcive CTE

• DELETE