Microsoft KB Archive/222657

= INF: Storing a Directory Tree and Maintaining a Full Path Using Recursive Triggers =

Article ID: 222657

Article Last Modified on 11/3/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q222657



SUMMARY
The ability to create recursive triggers is a new feature of SQL Server starting with version 7.0. This article contains a Transact-SQL script that demonstrates an example of using recursive triggers to create a parent/child self-joining relationship to store a directory tree and maintain a full path. This gives you the best of both worlds: great update capability of the parent/child relationship and the query performance in getting the full path.



MORE INFORMATION
The following list outlines the steps taken in the code sample provided below. Please note that the first step is to configure the pubs database to allow recursive triggers.
 * 1) Configure the pubs database to allow recursive triggers.
 * 2) Move to the pubs database.
 * 3) Drop the tree table in case it already exists.
 * 4) Create the tree table.
 * 5) Insert values into the tree table.
 * 6) Create the tree_trg_upd trigger.
 * 7) Select everything from the tree table to display the contents for comparison.
 * 8) Begin the first transaction.
 * 9) Modify the 'root' directory name.
 * 10) Select everything from the tree table for comparison.
 * 11) Roll back the first transaction.
 * 12) Begin a second transaction.
 * 13) Modify the 'x86' subdirectory name.
 * 14) Select everything from the tree table to confirm they were all updated correctly.
 * 15) Roll back the second transaction.

The following is the Transact-SQL script that demonstrates recursive triggers: sp_dboption 'pubs', 'recursive triggers', TRUE GO

USE pubs GO

DROP TABLE tree GO CREATE TABLE tree(id INT, pid INT NULL, name VARCHAR(40), fullname VARCHAR(512)) GO INSERT INTO tree VALUES (1, null, 'root'   ,'root') INSERT INTO tree VALUES (2, 1,   'x86'     ,'root\x86') INSERT INTO tree VALUES (3, 2,   'retail'  ,'root\x86\retail') INSERT INTO tree VALUES (4, 3,   'bin'     ,'root\x86\retail\bin') INSERT INTO tree VALUES (5, 3,   'include' ,'root\x86\retail\include') INSERT INTO tree VALUES (6, 3,   'lib'     ,'root\x86\retail\lib') INSERT INTO tree VALUES (7, 5,   'mfc'     ,'root\x86\retail\include\mfc') GO

CREATE TRIGGER tree_trg_upd ON tree FOR UPDATE AS IF (@@ROWCOUNT > 0) BEGIN IF (UPDATE (name)) BEGIN UPDATE TREE SET TREE.fullname = CASE WHEN PARENT.fullname IS NOT NULL THEN PARENT.fullname + '\' ELSE '' END + INSERTED.name FROM INSERTED, tree, TREE PARENT WHERE INSERTED.ID = tree.ID     AND INSERTED.PID *= PARENT.ID   END IF (UPDATE (fullname)) BEGIN UPDATE tree SET fullname = INSERTED.fullname + '\' + tree.name FROM tree, INSERTED WHERE INSERTED.id = tree.pid END END GO

SELECT * FROM tree GO

BEGIN TRANSACTION GO UPDATE tree SET name = 'base_root' WHERE name = 'root' GO SELECT * FROM tree GO ROLLBACK TRANSACTION GO

BEGIN TRANSACTION GO UPDATE tree SET name = 'i386' WHERE name = 'x86' GO SELECT * FROM tree GO ROLLBACK TRANSACTION GO

DROP TABLE tree GO

Additional query words: tsql t-sql

Keywords: kbhowto kbinfo KB222657

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.