title: how to delete a tree in a self referencing table?
body: <p>I have the following table:</p>
  
  <pre><code>MyTree(IDNode, IDRootNode, IDParentNode, Left, Right, Depth, ...)
  </code></pre>
  
  <p>IDNode is the primary key that identiy the node.
  IDRootNode is the root node.
  IDParentNode is the parent node of the node.
  Left and Right is the position of the node. IS not important for the delete.</p>
  
  <p>I see the following code, is a trigger, that let delete registers from a self referencing table:</p>
  
  <pre><code>CREATE TRIGGER MyTable_OnDelete ON MyTable
  INSTEAD OF DELETE
  AS 
  BEGIN
  
    SET NOCOUNT ON;
  
    DELETE FROM mt
    FROM   deleted AS D
    JOIN   MyTable AS mt
    ON     d.Id = mt.ParentId
  
    DELETE FROM mt
    FROM   deleted AS D
    JOIN   MyTable AS mt
    ON     d.Id = mt.Id
  
  END
  </code></pre>
  
  <p>If I am not wrong, first delete the childs of a node that I want to delete, and later delete the node. But if a child has childs, are deleted too or only delete the childs of the node and not the childs of the childs?</p>
  
  <p>And with recrsive CTE how would it be?</p>
  
  <p>Thanks.</p>