Recursive Delete in SQL Server

The Problem

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.

This behavior is documented on MSDN for SQL Server 2000 and SQL Server 2005.

The Solution

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.