SQL Server Stored Procedure ON DELETE

Vote count:

0

I have a table with a recursive relationship that has a foreign key constraint for ID and ParentID.

MyTable Schema
------------------
ID
Name
ParentID

I'm not enforcing the Foreign Key constraint and have to leave the CASCADING to No Action because it would cause cyclic cascades (which is what I want). When I delete a record that has children, I still want to automatically delete all its children (i.e. DELETE FROM MyTable WHERE ParentID = THIS_RECORD_I_JUST_DELETED). This would invoke the procedure recursively until all the children and their children were deleted.

How can I write a SQL Server Procedure to do this. I know there is ON UPDATING or ON DELETING type clauses but can't find anything related when I Google it.

asked 38 secs ago

jth_92

392