当注释被删除时，如何使用注释系统删除注释的所有后代？ [英] How do I make a comment system that deletes all descendants of a comment when the comment is deleted?

问题描述

我正在制作一个完整的网络应用程序，并尽可能在数据库中写入尽可能多的逻辑。

I'm making a full-stack web application and am trying to write as much logic in the database as possible.

我有一个注释系统，如

I have a comment system like

    1
   / \
  2   3
 / \
4   5

我正在代表

   Comments
===============
  id | ...
---------------
   1 | ...
   2 | ...
   3 | ...
   4 | ...
   5 | ...

           CommentTree
===================================
  id | ancestor | descendant | ...
-----------------------------------
  1  |     1    |      1     | ...
  2  |     1    |      2     | ...
  3  |     1    |      3     | ...
  4  |     1    |      4     | ...
  5  |     1    |      5     | ...
  6  |     2    |      2     | ...
  7  |     2    |      4     | ...
  8  |     2    |      5     | ...
  9  |     3    |      3     | ...
  10 |     4    |      4     | ...
  11 |     5    |      5     | ...

我想知道如何设置，以便在删除评论时，所有后代都被删除。当祖先被删除时，我知道如何设置删除对祖先 - 后代关系的引用：

and I'm wondering how I can set up so that when a comment is deleted, all its descendants are deleted. I know how to set it up to delete the references to the ancestor-descendant relationship when the ancestor is deleted:

 FOREIGN KEY (ancestor) REFERENCES Comments(id) ON DELETE CASCADE

但是如何使 操作触发后代评论也被删除？

but how can I make that action trigger that the descendant comment to be deleted as well?

换句话说，发生以下事件链

In other words, the following chain of events occurs

1. 注释已删除


2. 具有与该注释ID相同的祖先的树中的所有行都将被删除


3. 删除所有与步骤2中删除的后代相同的注释的注释

我有步骤1和2 ，但是如何在那里工作第3步？每个树元素被删除时，是否必须编写一个被触发的过程？你能给我一个例子吗？

I have steps 1 and 2 down, but how can I work step 3 in there? Do I have to write a procedure that gets triggered when each tree element is deleted? Can you show me an example of how this would look?

推荐答案

我不认为可以实现使用约束。这是因为会发生循环。你可以尝试使用触发器来实现它，但是我不推荐它，因为可见性将非常低（我甚至不确定是否可能）。

I don't think it's possible to achieve using constraints. It's because of loops that will occur. You could try to implement it using triggers but I don't recommend it as visibility will be very low (and I am not even sure if it's possible).

我建议您要删除评论时要调用的存储过程。更容易维护，可见性更好。

I suggest store procedure that you would call when you want to delete comment. It's much easier to maintain and visibility is better.

create procedure DeleteComment(
  @CommentID int
  )
  as 
 declare
  @CommentToDelete TABLE (id int);
  begin

  -- save comments for deletion
  insert into @CommentToDelete 
  select descendant from CommentTree  
  where  ancestor = @CommentID;

  -- delete relation from tree
with tree (commentTreeID, ancestor, descendant , path,  src) as 
(
select id,  ancestor,  descendant , cast ( '-'+ cast(id as varchar(2000)) +'-' as varchar(2000))   , 0from 
CommentTree ct
where ct.ancestor = @CommentID
union all
select CT.Id, CT.ancestor,  CT.descendant ,cast( t.path + '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), 1
from tree t
join CommentTree CT 
 on CT.ancestor = t.descendant and
  CHARINDEX (cast( '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), t.path) = 0 and
  t.src != 2
union all
 select CT.Id, CT.descendant,  CT.ancestor ,cast( t.path + '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), 2
from tree t
join CommentTree CT 
 on CT.descendant = t.descendant and
 CHARINDEX(cast( '-' + cast(id as varchar(2000)) +'-' as varchar(2000)), t.path) =0  and
   t.src != 2
 ) 
 delete CT 
  from CommentTree CT 
  join tree t 
  on t.commentTreeID = CT.ID;

  -- now we can delete comments
  delete Comments
  where id in (select id from  @CommentToDelete);

end;

• 第一个连接用于下载树（src = 1 ）。


• 第二个连接用于上传树（src = 2）。



当我们上去，我们不想再下来或再次下来=> t.src！= 2 使它发生。

When we go up we don't want to go down or up again = > t.src != 2 makes it happen.



• CHARINDEX（...）保护我们免受循环


• CHARINDEX (... ) protects us from loops.

这篇关于当注释被删除时，如何使用注释系统删除注释的所有后代？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！