自动维护路径信息的Sql Server无限级目录表
我做的网站基本上都要用到一个无限级的目录表，用来分门别类组织各种文章类的信息。这个表只有三个字段(Id,ParentId,Name)，一直就是这样用也没出现过什么问题。直到昨天，碰到了一个问题。我需要知道某个目录下面所有的子孙目录都有哪些。如果以Id或ParentId来查找的话，只能取得父Id和子Id，却不知道爷爷Id、老爷Id、孙子Id、重孙Id...。
于是就在博问上发表了问题：http://space.cnblogs.com/question/3963/
有园友提示在表中增加一个路径字段，也有园友给出了程序的实现代码，以循环方式来取得。我觉得在表中记录路径信息在使用当中相对更方便一些，效率可能也更高一些。
但是要维护这些路径信息也够麻烦的，每次增删改一个目录都要修改牵涉到它的每条记录。于是乎:-) 我就考虑能不能在表上做个触发器，自动完成这些任务呢。
说干就干，首先修改目录表的结构，增加两个字段(IdPath, NamePath)，如下图：
再写一个存储过程。接受一个@Id参数，来更新Id=@Id的目录的路径信息。代码如下：
1 CREATE PROCEDURE [dbo].[SetSortPath]
2 @Id int
3 AS
4 SET NOCOUNT ON;
5
6 DECLARE @PId varchar(10),
7 @ParentId int,
8 @Name nvarchar(500),
9 @IdPath varchar(500),
10 @NamePath nvarchar(500);
11
12 SET @IdPath='/';
13 SET @NamePath='/';
14 SELECT @PId=[ParentId] FROM [Sort] WHERE [Id]=@Id;
15
16 WHILE (@PId IS NOT NULL)
17 BEGIN
18 SELECT @ParentId=[ParentId], @Name=[Name] FROM [Sort] WHERE [Id]=@PId;
19
20 SET @IdPath = '/' + @PId + @IdPath;
21 SET @NamePath = '/' + @Name + @NamePath;
22
23 IF(@ParentId <> @PId)
24 SET @PId=@ParentId;
25 ELSE
26 SET @PId=NULL;
27 END
28
29 IF(@IdPath='/')
30 UPDATE [Sort] SET [IdPath]=NULL, [NamePath]=NULL WHERE [Id]=@Id;
31 ELSE
32 UPDATE [Sort] SET [IdPath]=@IdPath, [NamePath]=@NamePath WHERE [Id]=@Id;
2 @Id int
3 AS
4 SET NOCOUNT ON;
5
6 DECLARE @PId varchar(10),
7 @ParentId int,
8 @Name nvarchar(500),
9 @IdPath varchar(500),
10 @NamePath nvarchar(500);
11
12 SET @IdPath='/';
13 SET @NamePath='/';
14 SELECT @PId=[ParentId] FROM [Sort] WHERE [Id]=@Id;
15
16 WHILE (@PId IS NOT NULL)
17 BEGIN
18 SELECT @ParentId=[ParentId], @Name=[Name] FROM [Sort] WHERE [Id]=@PId;
19
20 SET @IdPath = '/' + @PId + @IdPath;
21 SET @NamePath = '/' + @Name + @NamePath;
22
23 IF(@ParentId <> @PId)
24 SET @PId=@ParentId;
25 ELSE
26 SET @PId=NULL;
27 END
28
29 IF(@IdPath='/')
30 UPDATE [Sort] SET [IdPath]=NULL, [NamePath]=NULL WHERE [Id]=@Id;
31 ELSE
32 UPDATE [Sort] SET [IdPath]=@IdPath, [NamePath]=@NamePath WHERE [Id]=@Id;
最后是给目录表创建一个触发器。任务是每次添加或修改目录，就从inserted表中遍历每条记录，使用上面的存储过程更新它和它的子孙目录。代码如下：
1 CREATE TRIGGER [dbo].[Trig_SetPath]
2 ON [dbo].[Sort]
3 AFTER INSERT, UPDATE
4 AS
5 SET NOCOUNT ON;
6
7 DECLARE @Id varchar(11);
8
9 DECLARE cur_Inserted CURSOR FOR SELECT [Id] FROM Inserted; --从Inserted表创建游标
10 OPEN cur_Inserted;
11
12 FETCH FROM cur_Inserted INTO @Id;
13 WHILE(@@Fetch_Status=0)
14 BEGIN
15
16 EXEC [SetSortPath] @Id=@Id; --更新自身
17
18 DECLARE cur_Sort CURSOR FOR SELECT [Id] FROM [Sort] WHERE [IdPath] LIKE '%/'+@Id+'/%'; --以所有子孙目录创建游标
19 OPEN cur_Sort;
20 FETCH FROM cur_Sort INTO @Id;
21 WHILE(@@Fetch_Status=0)
22 BEGIN
23 EXEC [SetSortPath] @Id=@Id; --更新子目录
24 FETCH FROM cur_Sort INTO @Id;
25 END
26 CLOSE cur_Sort;
27 Deallocate cur_Sort;
28
29 FETCH FROM cur_Inserted INTO @Id;
30
31 END
32 CLOSE cur_Inserted;
33 Deallocate cur_Inserted;
2 ON [dbo].[Sort]
3 AFTER INSERT, UPDATE
4 AS
5 SET NOCOUNT ON;
6
7 DECLARE @Id varchar(11);
8
9 DECLARE cur_Inserted CURSOR FOR SELECT [Id] FROM Inserted; --从Inserted表创建游标
10 OPEN cur_Inserted;
11
12 FETCH FROM cur_Inserted INTO @Id;
13 WHILE(@@Fetch_Status=0)
14 BEGIN
15
16 EXEC [SetSortPath] @Id=@Id; --更新自身
17
18 DECLARE cur_Sort CURSOR FOR SELECT [Id] FROM [Sort] WHERE [IdPath] LIKE '%/'+@Id+'/%'; --以所有子孙目录创建游标
19 OPEN cur_Sort;
20 FETCH FROM cur_Sort INTO @Id;
21 WHILE(@@Fetch_Status=0)
22 BEGIN
23 EXEC [SetSortPath] @Id=@Id; --更新子目录
24 FETCH FROM cur_Sort INTO @Id;
25 END
26 CLOSE cur_Sort;
27 Deallocate cur_Sort;
28
29 FETCH FROM cur_Inserted INTO @Id;
30
31 END
32 CLOSE cur_Inserted;
33 Deallocate cur_Inserted;
我简单测试了一下，效果还行，支持批量添加和批量修改。
