December 01, 2001 03:01 PM

Using a Recursive Trigger to Delete a Subtree

Rating: (0)
SQL Server Magazine
InstantDoc ID #23123

Q. Listing 1 creates a table named Tree that represents a hierarchical structure, and Figure 1 shows the hierarchical relationships among the tree's nodes. When I delete a specific row, I want the operation also to delete the entire subtree (i.e., all of that node's children). For example, if I delete the row containing childid = 2, I want the rows with childids 5, 6, 7, and 8 to be deleted as well. And when I delete the row that has childid = 1, all rows should disappear. How can I achieve these results?

...

You must be a paid Professional Member to access this entire article.

Already a Professional Member? Please log in now:

NOT A PROFESSIONAL MEMBER? YOU CHOOSE:

Monthly or Annual

Professional Membership

VIP Membership

Compare Member Benefits

Add a Comment

thanks! you definitely simplified things for me! works great!!!

Anonymous User 3/30/2005 11:37:29 AM


Excellent article, very useful, saved me a lot of research and testing! Thanks

Anonymous User 3/9/2005 4:07:55 AM


how could you make this work on an access database with a self referencing table of arbitrary levels?

Anonymous User 2/2/2005 3:46:48 PM


set RECURSIVE_TRIGGERS in the new database

Anonymous User 1/12/2005 3:57:18 PM


I have tried this and it works on a single sql server (2000) but when I replicate the database this no longer works - it doesn't call itself anymore.

Does this mean you can't use recursion and replication together? If so what other options are there (replication is needed)

jim10/23/2003 2:32:06 AM


You must log on before posting a comment.

Are you a new visitor? Register Here

Windows Event 333->

We have a VM that has 8GB memory and is running Windows 2003 Enterprise Server 32bit and SQL Server 2000 SP4.Making no changes the system runs fine an...222-96226

GOOGLE LINKS
SPONSORED LINKS
FEATURED LINKS