>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 :-)))))))))))
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.