Although you can define a foreign key with CASCADE DELETE in SQL Server, recursive cascading deletes are not supported (i.e. cascading delete on the same table).
If you create an INSTEAD OF DELETE trigger, this trigger only fires for the first DELETE statement, and does not fire for records recursively deleted from this trigger.
Suppose you have a table defined like this:
CREATE TABLE MyTable ( OID INT, -- primary key OID_Parent INT, -- recursion ... other columns )
then the delete trigger looks like this:
CREATE TRIGGER del_MyTable ON MyTable INSTEAD OF DELETE AS CREATE TABLE #Table( OID INT ) INSERT INTO #Table (OID) SELECT OID FROM deleted DECLARE @c INT SET @c = 0 WHILE @c <> (SELECT COUNT(OID) FROM #Table) BEGIN SELECT @c = COUNT(OID) FROM #Table INSERT INTO #Table (OID) SELECT MyTable.OID FROM MyTable LEFT OUTER JOIN #Table ON MyTable.OID = #Table.OID WHERE MyTable.OID_Parent IN (SELECT OID FROM #Table) AND #Table.OID IS NULL END DELETE MyTable FROM MyTable INNER JOIN #Table ON MyTable.OID = #Table.OID GO
The trigger first inserts all records from the deleted pseudo table into the record collection table #Table. Then it collects all detail records which are not already in the table of collected records (LEFT OUTER JOIN … WHERE IS NULL). The loop stops if no new records (i.e. no new detail records) are found. Finally, all collected records are deleted.