473,320 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Tree table

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
4 5744
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
>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
Andrzej,

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

Sep 12 '06 #4
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Xah Lee | last post by:
Here's the belated Java solution. import java.util.List; import java.util.ArrayList; import java.lang.Math; class math { public static List range(double n) { return range(1,n,1); }
0
by: Dinesh | last post by:
Hi, I have two tables 'master' and 'child', the master is the master table for all nodes in all trees. To get children of any node, we need to go to the 'child' table to get the nodeid of the...
0
by: t_pet422 | last post by:
Hi, I've been scouring the net and reading the PostgreSQL docs for a while now trying to learn how to create a recursive function in PL/pgSQL that will return a whole subtree given a starting...
2
by: ragha | last post by:
Dear friends I am emulating thr tree structure mentioned in the article http://www.15seconds.com/issue/010921.htm I have succesfully created the folder structure for level 2 I need this...
0
by: Daniel Hirscher | last post by:
Hi, I like to transform a XML hierarchical tree structure into a HTML Table. All leaves are on the same depth of the tree. Every tree will give a correct table without empty cells. The number...
4
by: bob_yohan | last post by:
Hi all, I am rather new to database design and modelling concepts in general and was hoping for some advice on a problem I am trying to solve. I have designed a piece of software that creates a...
1
by: Srihari | last post by:
I'm trying to develop a tree structure using javascript. The node values of the tree are generating from a mysql table depending on login. The tree structure contains 3 sub levels. I developed...
0
by: Tree menu using XML | last post by:
I have one XML file that has nodes and sub node and each and every node has the attribute call visible if its value is true then diplay this node else don't display thid node, but this condition i...
3
by: Tzvika Barenholz | last post by:
Hi all. Here's my problem: I have a tree linking macaddresses (bigints) in a tree structure. i want to get the path from node a to b. create table tree1(father bigint , child bigint); ...
19
by: Christian Fowler | last post by:
I have a VERY LARGE pile of geographic data that I am importing into a database (db of choice is postgres, though may hop to oracle if necessary). The data is strictly hierarchical - each node has...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.