Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Nico
Starting Member
18 Posts |
 Posted - 2002-08-28 : 10:01:45
|
| Hello,I have a table called Component, and it's made up from:- id- fk_Component- nameWith this I can create a tree structure:Component 1 (id=1, fk_Component=0)- Component 1.1 (id=2, fk_Component=1)-- Component 1.1.1 (id=4, fk_Component=2)- Component 1.2 (id=3, fk_Component=1)When I delete Component 1, I'd like to delete all children as well. Now it's fairly simple to write a recursive function that checks whether a node has children and deletes them, but I'm wondering if it could be done in one single transaction (in a stored procedure).With SQL Enterprise Manager I can create relationships between tables and enforce cascading update/delete, but this won't work for the Component table (maybe because it links back to itself?).Any ideas?Kind regards, Nico |
|
|
swordfish
Starting Member
11 Posts |
Posted - 2002-08-28 : 10:07:58
|
| Could you write a delete trigger for the table that deletes the child rows if the parent is removed? |
 |
|
|
Nico
Starting Member
18 Posts |
Posted - 2002-08-28 : 11:03:53
|
| I found where I can manage triggers, but it's been ages since I did anything with triggers so I won't be able to come up with a decent definition myself. Maybe I can find something in my books at home.Thanks for the hint though. I'm sure it will work.On a sidenote, I also need to update a field in a parent component, but only if the child component I deleted was the only child left.So what I need to do is:- query the fk_Component for the selected component- count the number of components that have a similar fk_Component- if the count equals 0 (or 1, depending on when I decide to delete the selected childcomponent), then update a field in the component for which id=fk_ComponentI can do this with 3 stored procedures, but it seems to me that 1 should be enough.Kind regards, Nico |
|
|
|
Nico
Starting Member
18 Posts |
Posted - 2002-08-29 : 07:21:18
|
| In case anyone is interested in the solution:Step 1.)Turn on the "recursive triggers" option for the database.Step 2.)Create a trigger on the table (in my case "Component")CREATE TRIGGER tr_casc_deleteON ComponentFOR DELETE ASIF @@ROWCOUNT = 0 RETURNDELETE ComponentWHERE Component.fk_Component = idDo not forget to check for the rowcount, otherwise you'll end up in and endless loop (which halts at the 32nd level, which is the deepest you can go).Kind regards, Nico |
|
|
|
|
|
|
|
|
Cascading delete