当注释被删除时，如何使用注释系统删除注释的所有后代？ | 如何使用注释系统删除注释的所有后代

本文介绍了当注释被删除时，如何使用注释系统删除注释的所有后代？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

我正在代表

   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

注释已删除

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

删除所有与步骤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.

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