Re: Tree table
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 12 Sep 2006 21:42:59 +0000 (UTC)
Andrzej Jaworek (SPAMandrzejjaworekSPAM@xxxxx) 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, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- References:
- Tree table
- From: Andrzej Jaworek
- Tree table
- Prev by Date: Re: Best practice for storing long text fields
- Next by Date: Re: Memory setting right ?
- Previous by thread: Re: Tree table
- Next by thread: Find out the percentage complete of Stored Procedure
- Index(es):
Relevant Pages
|
