1  --  The ability to create recursive triggers is a new feature of SQL Server
 2  --  starting with version 7.0. This article contains a Transact-SQL script that
 3  --  demonstrates an example of using recursive triggers to create a parent/child
 4  --  self-joining relationship to store a directory tree and maintain a full path.
 5  --  This gives you the best of both worlds: great update capability of the
 6  --  parent/child relationship and the query performance in getting the full path. 
 7  --
 8  sp_dboption 'pubs', 'recursive triggers', TRUE
 9  GO
10
11  USE pubs
12  GO
13
14  DROP TABLE tree
15  GO
16  CREATE TABLE tree(id INT, pid INT NULL, name VARCHAR(40), fullname VARCHAR(512))
17  GO
18  INSERT INTO tree VALUES (1, null, 'root'    ,'root')
19  INSERT INTO tree VALUES (2, 1,    'x86'     ,'root\x86')
20  INSERT INTO tree VALUES (3, 2,    'retail'  ,'root\x86\retail')
21  INSERT INTO tree VALUES (4, 3,    'bin'     ,'root\x86\retail\bin')
22  INSERT INTO tree VALUES (5, 3,    'include' ,'root\x86\retail\include')
23  INSERT INTO tree VALUES (6, 3,    'lib'     ,'root\x86\retail\lib')
24  INSERT INTO tree VALUES (7, 5,    'mfc'     ,'root\x86\retail\include\mfc')
25  GO
26
27  CREATE TRIGGER tree_trg_upd
28  ON tree
29  FOR UPDATE
30  AS
31  IF (@@ROWCOUNT > 0) BEGIN
32     IF (UPDATE (name)) BEGIN
33        UPDATE TREE
34        SET TREE.fullname = CASE
35              WHEN PARENT.fullname IS NOT NULL
36                 THEN PARENT.fullname + '\''
37              ELSE ''
38           END
39           + INSERTED.name
40        FROM INSERTED, tree, TREE PARENT
41        WHERE INSERTED.ID = tree.ID
42        AND INSERTED.PID *= PARENT.ID
43     END
44     IF (UPDATE (fullname)) BEGIN
45        UPDATE tree
46        SET fullname = INSERTED.fullname + '\'' + tree.name
47        FROM tree, INSERTED
48        WHERE INSERTED.id = tree.pid
49     END
50  END
51  GO
52
53  SELECT * FROM tree
54  GO
55
56  BEGIN TRANSACTION
57  GO
58  UPDATE tree
59  SET name = 'base_root'
60  WHERE name = 'root'
61  GO
62  SELECT * FROM tree
63  GO
64  ROLLBACK TRANSACTION
65  GO
66
67  BEGIN TRANSACTION
68  GO
69  UPDATE tree
70  SET name = 'i386'
71  WHERE name = 'x86'
72  GO
73  SELECT * FROM tree
74  GO
75  ROLLBACK TRANSACTION
76  GO
77
78  DROP TABLE tree
79  GO