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 4 5782
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.
>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.
Andrzej,
google up "materializ ed path". If you go for it, your deletes will be
very simple.
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****@sommarsk og.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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);
}
|
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 children. The master has
about 40,000 such trees with about 400 nodes in each tree.
The input to me is the 'Root Node'/'First Node' of a tree. I need to
traverse thru all the child nodes starting from 'Root Node' ( and
process it ) and then...
|
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 node. I wanted to
share my summary results here. Maybe this will help someone and save
them from doing a bunch of research (like I had to do :). For the
record, this is the first thing I've ever written in PL/pgSQL,
although I do have significant...
|
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 structure till 5 level is there any easier way to generate this
using xsl
My xml tree node is
|
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 of elements in one
hierarchy level can be more than two.
My problem is that I have to go into tree-depth and table-width at the
same time. Calculation of colspan is no problem.
| |
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 tree with pluggable
nodes. Each node class can have 0 to n distinct classes plugged into
it to define the type for that node.
For example, a node plugged with a 'customer' class and an 'engineer'
class would indicate that this node in the...
|
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 static HTML tree using http://www.treeview.net. now i need to
generate this tree dynamically.
Can any one has code for this?
|
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 am able to check using xpath in
asp.net 2.0 till MenuItem node. if i check visible attribute value till
SubMenuLevel0 node then in tree it will not display the MenuItem Node at all
Note: My tree Menu will start from MenuItem node and it will...
|
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);
insert into tree1 (father,child) values (100,200);
insert into tree1 (father,child) values (100,300);
insert into tree1 (father,child) values (100,400);
|
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 one, and only one
parent. The depth should not exceed 6 or 7 levels. The initial import will have about 6 million leaves, and
3 million branches. I would expect the leaves to grow significantly, in number easily tripling. However, the
branches will...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |