自动维护路径信息的Sql Server无限级目录表

我做的网站基本上都要用到一个无限级的目录表，用来分门别类组织各种文章类的信息。这个表只有三个字段(Id,ParentId,Name)，一直就是这样用也没出现过什么问题。直到昨天，碰到了一个问题。我需要知道某个目录下面所有的子孙目录都有哪些。如果以Id或ParentId来查找的话，只能取得父Id和子Id，却不知道爷爷Id、老爷Id、孙子Id、重孙Id...。

于是就在博问上发表了问题：http://space.cnblogs.com/question/3963/

有园友提示在表中增加一个路径字段，也有园友给出了程序的实现代码，以循环方式来取得。我觉得在表中记录路径信息在使用当中相对更方便一些，效率可能也更高一些。

但是要维护这些路径信息也够麻烦的，每次增删改一个目录都要修改牵涉到它的每条记录。于是乎:-) 我就考虑能不能在表上做个触发器，自动完成这些任务呢。

说干就干，首先修改目录表的结构，增加两个字段(IdPath, NamePath)，如下图：

再写一个存储过程。接受一个@Id参数，来更新Id=@Id的目录的路径信息。代码如下：

1CREATEPROCEDURE[dbo].[SetSortPath]
2@Id　　int
3AS
4SETNOCOUNTON;
5
6DECLARE　　@PId　　　　varchar(10),
7　　@ParentId　　int,
8　　@Name　　　　nvarchar(500),
9　　@IdPath　　　　varchar(500),
10　　@NamePath　　nvarchar(500);
11
12SET@IdPath='/';
13SET@NamePath='/';
14SELECT@PId=[ParentId]FROM[Sort]WHERE[Id]=@Id;
15
16WHILE(@PIdISNOTNULL)
17BEGIN
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;
27END
28
29IF(@IdPath='/')
30　　UPDATE[Sort]SET[IdPath]=NULL,[NamePath]=NULLWHERE[Id]=@Id;
31ELSE
32　　UPDATE[Sort]SET[IdPath]=@IdPath,[NamePath]=@NamePathWHERE[Id]=@Id;