自動維護路徑信息的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;