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
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
A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results.
You can define the maximum number of recursions for CTE, using the MAXRECURSION option. Set the value of MAXRECURSION to 0, if you don't know the exact numbers of recursions.
Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
I suppose you see complexity where it does not exists.
Your mistake is:
Deleting rows recursively in self-referencing CTE
There is no such thing as recursive DELETE
. Only SELECT
can be.
So processing is simple as:
Calculate all rows for deletion in SELECT
with recurcive CTE
DELETE
them all with one operation
That's all
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With