sql server, cascade delete and parent/child table

Question

i have one simple table with following columns: id, name and parentID

i created relationship diagram between id and parentID (on a same table), like simple tree, on the same table, but when i tried to user cascade delete it was disabled for me

i know that it will be recursive delete if i will delete parent it will delete his children

have i any options for anable cascade delete without triggers?

Answer 1

No, SQL Server does not allow recursive and/or multiple cascade paths.

You can use a stored procedure to delete bit by bit, or use a trigger. Sorry.

Answer 2

best to use an after trigger for multiple actions.