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

ID: Q222657

The information in this article applies to:

SUMMARY

The ability to create recursive triggers is a new feature in SQL Server 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          : SSrvAdmin 
Version           : winnt:7.0
Platform          : winnt 
Issue type        : kbhowto kbinfo

Last Reviewed: March 30, 1999