How do I make a comment system that deletes all de

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

that I'm representing 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

Comment deleted
All rows in tree with ancestor equal to that comment's id are deleted
All comments whose ids are equal to the descendants deleted in step 2 are deleted

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;