Andrzej Jaworek (SP********************@o2.pl) writes:
I have a "tree" table:
Id - primary key
ParrentId - (foreign key) related to Id
Title
....
when I delete some record I want to delete it with all childs (cascade
deleting). I can't set cascade deleting on the same table :(. Is there
any easy way in the MSSQL 2005 to do this ? There is one idea - using
cursors + recursive functions but I think this solution is not easy
and elegant.
An INSTEAD OF trigger and a recursive CTE is the way to go:
CREATE TABLE hierarchy(id int NOT NULL PRIMARY KEY,
parent int NULL REFERENCES hierarchy(id))
go
CREATE TRIGGER hier_delete ON hierarchy INSTEAD OF DELETE AS
WITH CTE AS (
SELECT id, parent
FROM hierarchy
WHERE id IN (SELECT id FROM deleted)
UNION ALL
SELECT h.id, h.parent
FROM hierarchy h
JOIN CTE ON h.parent = CTE.id
)
DELETE hierarchy
FROM hierarchy h
JOIN CTE ON h.id = CTE.id
go
INSERT hierarchy(id, parent)
EXEC('SELECT 1, NULL
SELECT 10, 1
SELECT 11, 1
SELECT 12, 1
SELECT 20, 10
SELECT 21, 10
SELECT 110, 11
SELECT 111, 11
SELECT 112, 11
SELECT 120, 12
SELECT 1101, 110')
go
SELECT * FROM hierarchy ORDER BY id
DELETE hierarchy WHERE id IN (10, 12)
SELECT * FROM hierarchy ORDER BY id
go
DROP TABLE hierarchy
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
