9

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
eddy
  • 4,373
  • 16
  • 60
  • 94
  • 1
    I think this excellent question from dba.se is relevant: [Unique index updates and statistics row modification counters](https://dba.stackexchange.com/q/185551/57105). Examine the actual execution plan of your `DELETE`, it would show you a lot of details. – Vladimir Baranov Sep 13 '17 at 05:38

1 Answers1

2

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:

  1. Calculate all rows for deletion in SELECT with recurcive CTE

  2. DELETE them all with one operation

That's all

Alex Yu
  • 3,412
  • 1
  • 25
  • 38
  • Yes, you're right. Perhaps the way I worded the title is a bit misleading since what is calculated recursively are the IDs, BUT there's gotta be some kind of recursive process in the way the comments are deleted using the trigger. Surely they can't be deleted all at once without causing a referential integrity error? If I'm not mistaken, the first time a try to delete the comment of id 3, the CTE should return these ids: `3,8, 13`. If the deletion was to happen all at once, then trying to delete comment 3 should not be allowed since it has a child comment (8) – eddy Sep 13 '17 at 15:25
  • **"Surely they can't be deleted all at once without causing a referential integrity error"** - Why? @eddy, you wrote correct SELECT CTE calculating delta, there are no nested transactions, SQLServer **must** delete all the records at once. (In fact, I remember some RDBMS with triggers working "by-row". In combination with dirty reads it could be possible make _strange_ things. ) – Alex Yu Sep 13 '17 at 16:00
  • In fact, you can substitute your `DELETE` with combination of `INSERT` and `SELECT` with `NOT EXISTS`: 1) Create table DELETED_ROWS 2) `ALTER TRIGGER` - instead of `DELETE` you `INSERT INTO DELETED_ROWS`, 3) `CREATE VIEW ` for `COMMENTS C WHERE NOT EXISTS(SELECT * FROM DELETED_ROWS D WHERE D.id = C.id)` . In fact SQLServer do something similar - it does not purge everything immediately on issued `DELETE`, it marks records as outdated. – Alex Yu Sep 13 '17 at 16:14
  • Yeah, you may be right, but you're not going to tell me this isn't a bit confusing, because it is and it's also not consistent with what one would expect from referential integrity. If I were to remove the trigger and try to delete, let's say comment 3, which have children, I would get this error: **The DELETE statement conflicted with the SAME TABLE REFERENCE constraint FK_Comment_Comment". The conflict occurred in database "Blogger", table "dbo.Comment", column 'parent_comment_id'.**, – eddy Sep 13 '17 at 19:37
  • Why no error is produced when I try to run this code at the end of the trigger : `DELETE FROM Comment WHERE id IN (3,8,13)` is what remains a mystery to me :( – eddy Sep 13 '17 at 19:40
  • 1
    @eddy - because all DML statements act "as if" all of their actions occur in parallel. They don't act as if they first delete one row, then another, then a third, etc. The system is designed to work with *sets* of rows. – Damien_The_Unbeliever Sep 14 '17 at 14:43