An infinite directory table of SQL Server that automatically maintains path information

I do the site basically have to use an infinite level of table of contents, used to organize various types of article information. This table has only three fields (Id,parentid,name), which has always been the case with no problems. It was not until yesterday that I met a problem. I need to know what all the children's directories are under a directory. If the ID or ParentID to find, you can only get the parent ID and child ID, but do not know Grandpa ID, master ID, grandson ID, Scion id ....

So he asked questions on the blog: http://space.cnblogs.com/question/3963/

There are garden friends to add a path field in the table, but also have friends to give the program implementation code, in a circular way to obtain. I think it is more convenient and more efficient to record path information in a table.

However, to maintain these path information is troublesome enough, each deletion and modification of a directory to modify the related to its every record. So:-) I'll think about whether I can make a trigger on the table and automate these tasks.

Say dry, first modify the structure of the table of contents, add two fields (Idpath, Namepath), the following figure:

Write a stored procedure again. Accept a @id parameter to update the path information for the id= @Id directory. The code is as follows:

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;