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.

About these ads

14 Responses to Recursive Delete in SQL Server

  1. Jeannette says:

    This is great! Thanks!

  2. Sam says:

    THANKS!

  3. Simon Poulton says:

    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!

  4. Christophe says:

    Thanks. Came in handy.

  5. Adlangx says:

    Much obliged!

  6. Smokey says:

    SQL Server is so retarded

  7. Gilad says:

    Nice solution, using @@rowcount can solve 2*SELECT COUNT queries for each iteration

  8. [...] n’est trouvé. Finalement, tous les documents recueillis sont supprimés. (Adapté de devioblog)   If you enjoyed this article, please consider sharing [...]

  9. kwicia says:

    Tanks a lot

  10. Bruce says:

    Works a treat, thanks.

  11. sanyo says:

    What about using TSQL obfuscator? it may add a few more very nice cycles and other puzzle ыегаа automatically

  12. sanyo says:

    just some things to think about:

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

  13. estrnod says:

    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!

  14. Ibrahim Islam says:

    Thanks! You are a genius!!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: