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:

    OID    INT,        -- primary key
    OID_Parent INT,    -- recursion
    ... other columns

then the delete trigger looks like this:

    CREATE TABLE #Table(
        OID    INT
    INSERT INTO #Table (OID)
    FROM    deleted

    SET @c = 0

        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

    DELETE  MyTable
    FROM    MyTable
    INNER JOIN #Table ON MyTable.OID = #Table.OID


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.

15 thoughts on “Recursive Delete in SQL Server

  1. Just a quick note to thank you for solving this very irritating problem. As a recent convert from MS Access (which many other people have commented allows cascade delete through referential integrity settings – even on self-referencing tables!) I have been astonished to find that this is not simply accomplished in SQL server. There is very little help on MSDN so thanks for speeding me on my way!

  2. Pingback: Suppression récursive sous MS SQL Server

  3. just some things to think about:

    delete SelfRefT where ParentId in (select Id from deleted);
    delete SelfRefT where Id in (select Id from deleted);

  4. Thank you for this! What a shame, cascading delete on a self-referencing FK works just FINE with PostgreSQL; have to jump through so many hoops to just get this and many other things working in Sql Server!

  5. Pingback: Importing mysqldump into MS SQL Server | devioblog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.