Using a Recursive Trigger to Delete a Subtree

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?

A. To carry out the DELETE operation you want, you can write a simple recursive trigger. However, this kind of trigger works only for trees with 32 or fewer levels, because 32 is the nesting limit that SQL Server 2000 and 7.0 support. In your script, make sure you turn on the database property called recursive triggers because this option's default is off. Also, use the recursion termination check

IF @@rowcount = 0 RETURN

which aborts the script as soon as the trigger deletes 0 rows. Listing 2 shows a sample recursive trigger script.

If you don't use the recursion termination check, a trigger will fire even if the modification affects 0 rows. In that case, the trigger would fire repeatedly until it exceeded the maximum 32 nesting levels and would roll back all previous activity. To understand how the trigger can reach the nesting level, consider the following example. When you delete the row that holds childid = 2, the trigger also deletes the rows with childid values 5 and 6. If the recursive triggers database property is turned on, the trigger fires a second time, deleting the children of 5 and 6 (i.e., the rows with childids 7 and 8). Then the trigger fires again and attempts to delete the children of 7 and 8. Although childids 7 and 8 have no children, a DELETE statement was issued, so the trigger fires one more time. If you haven't set the recursion termination check, another DELETE statement that attempts to delete the children of "no one" is issued, then another trigger fires, and so on, until the trigger reaches the nesting level, wasting resources as it goes. However, if the recursion termination check is enabled, when the DELETE statement returns no rows, the check determines that the branch or subtree was deleted and aborts any further activity.

To test the trigger, you can issue the following DELETE statement:

DELETE FROM TreeWHERE childid = 2

Then, you can execute

SELECT * FROM Tree

to examine the Tree table. Figure 2 shows that the rows containing childids 5, 6, 7, and 8 were also deleted.