Tree table

[Andrzej Jaworek]

Hello,

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.

Thakns for any help and sugestions.

Regards.

Andy

[Ed Murphy]

Re: Tree table

Andrzej Jaworek wrote:

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.

Another idea is to split ParentId off into a second table, but that's
probably even less easy and elegant.

[--CELKO--]

Re: Tree table

>I have a "tree" table: <<

Get a copy of TREES & HIERARCHIES IN SQL for several different ways to
model trees. Google "Nested Sets" model for one of them.

[Alexander Kuznetsov]

Re: Tree table

Andrzej,

google up "materializ ed path". If you go for it, your deletes will be
very simple.

[Erland Sommarskog]

Re: Tree table

Andrzej Jaworek (SPAMandrzejjaw orekSPAM@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, esquel@sommarsk og.se

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