使用CTE在自引用表中递归删除行。该过程如何进行？ [英] Deleting rows recursively in a self-referencing table using a CTE. How does the process take place?

问题描述

我正在开发一个辅助项目，为了删除自引用表中的一行及其所有后代，我在触发器内使用了
a递归CTE：

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

这是自引用表

尽管我的代码按预期运行，但是在这种情况下，做某事，但您不确定
是如何工作的。

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.

更确切地说，我想知道的是通过使用此递归CTE（ID），当我尝试删除具有子注释的注释时，能够避免引用完整性错误
？

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 

此处id 3的注释是根注释。评论8是对评论3的答复，就像评论13是对评论8的答复一样。

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?

PS我尝试添加一个表，在这些表中插入计算出的ID。不幸的是我无法理解。
这些表格的结果：

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

推荐答案

我想您会看到不存在的复杂性。

I suppose you see complexity where it does not exists.

您的错误是：

以自引用CTE递归删除行

Deleting rows recursively in self-referencing CTE

没有诸如递归 DELETE 之类的东西。只能使用 SELECT 。

There is no such thing as recursive DELETE. Only SELECT can be.

因此处理非常简单：

1. 使用递归CTE计算 SELECT 中所有要删除的行

Delete 只需一次操作即可全部删除

DELETE them all with one operation

就这些

这篇关于使用CTE在自引用表中递归删除行。该过程如何进行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！