By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

Tree table

P: n/a
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
Sep 12 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Sep 12 '06 #2

P: n/a
>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.

Sep 12 '06 #3

P: n/a
Andrzej,

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

Sep 12 '06 #4

P: n/a
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
Sep 12 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.