>Hi Boris,
>
>>The trigger looks OK, but did you have recursive triggers enabled?
>
>I've never heard of them. Is that a setting somewhere that can be switched on for the database?

EXEC sys.sp_configure N'nested triggers', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Unfortunately this is for the whole SQL Server, not only for DB. So you must reset it back.

>
>>How many levels of parenting you may have?
>
>It only goes one level deep.

If it is only one level deep, then your trigger should work.
Test it though :-)

But what I know is that the original design is never stays as it is :o)
Very soon you will have 3 or more levels :-))


>
>>What if you delete a child that has siblings?
>
>If a child is deleted, nothing else happens to the siblings. If a parent is deleted, then all children must be deleted.


As Naomi suggested you can use CTE to get all levels.

;WITH cteDelete (ID)
AS
(SELECT Id FROM Deleted
UNION ALL
SELECT Id FROM YourTable
INNER JOIN cteDelete ON YourTable.Parent = cteDelete.Id
)

DELETE YourTable
FROM YourTable
INNER JOIN cteDelete ON YourTable.Id = cteDelete.Id

this is NOT TESTED!!!
AT ALL :-)))))))))))