>>>
>>>The trigger looks OK, but did you have recursive triggers enabled?
>>>How many levels of parenting you may have?
>>>What if you delete a child that has siblings?
>>
>>I am thinking we want to disable recursive triggers and implement the whole loop logic in one trigger.
>
>
>It depends how many level you have :-(
>What if you have this:
>

>Parent
>   Child1
>   Child2
>       Grand Child1  
>       Grand Child2
>             Grand Grand Child1
>

>and you delete Parent?
>In this trigger you will delete Child1 and Child2 but not all Grands ;o)

This is exactly what I'm saying. Disable recursive triggers. Re-write the trigger's code to use correct approach to delete all children. Say, using recursive CTE we identify all records to delete and then perform the actual delete. We can use INSTEAD OF trigger to do this.

But in general such models are hard to handle.