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.
This is great! Thanks!
THANKS!
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!
Thanks. Came in handy.
Much obliged!
SQL Server is so retarded
Nice solution, using @@rowcount can solve 2*SELECT COUNT queries for each iteration
Pingback: Suppression récursive sous MS SQL Server
Tanks a lot
Works a treat, thanks.
What about using TSQL obfuscator? it may add a few more very nice cycles and other puzzle ыегаа automatically
just some things to think about:
delete SelfRefT where ParentId in (select Id from deleted);
delete SelfRefT where Id in (select Id from deleted);
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!
Thanks! You are a genius!!
Pingback: Importing mysqldump into MS SQL Server | devioblog