开发学院数据库MSSQL Server 自动维护路径信息的Sql Server无限级目录表 阅读

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

 2008-11-25 10:12:44 来源:WEB开发网   
核心提示:我做的网站基本上都要用到一个无限级的目录表,用来分门别类组织各种文章类的信息,自动维护路径信息的Sql Server无限级目录表,这个表只有三个字段(Id,ParentId,Name),一直就是这样用也没出现过什么问题,代码如下:1CREATETRIGGER[dbo].[Trig_SetPath]2ON[dbo].[S

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

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

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

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

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

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

再写一个存储过程。接受一个@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;

最后是给目录表创建一个触发器。任务是每次添加或修改目录,就从inserted表中遍历每条记录,使用上面的存储过程更新它和它的子孙目录。代码如下:

1CREATETRIGGER[dbo].[Trig_SetPath]
2  ON [dbo].[Sort]
3  AFTERINSERT,UPDATE
4AS
5SETNOCOUNTON;
6
7DECLARE@Id  varchar(11);
8
9DECLARE  cur_InsertedCURSORFORSELECT[Id]FROMInserted;  --从Inserted表创建游标
10OPENcur_Inserted;
11
12FETCHFROMcur_InsertedINTO@Id;
13WHILE(@@Fetch_Status=0)
14BEGIN
15
16  EXEC[SetSortPath]@Id=@Id;  --更新自身
17
18  DECLAREcur_SortCURSORFORSELECT[Id]FROM[Sort]WHERE[IdPath]LIKE'%/'+@Id+'/%';  --以所有子孙目录创建游标
19  OPENcur_Sort;
20  FETCHFROMcur_SortINTO@Id;
21  WHILE(@@Fetch_Status=0)
22  BEGIN
23    EXEC[SetSortPath]@Id=@Id;  --更新子目录
24    FETCHFROMcur_SortINTO@Id;
25  END  
26  CLOSE cur_Sort;
27  Deallocate cur_Sort;
28
29  FETCHFROMcur_InsertedINTO@Id;
30
31END
32CLOSE cur_Inserted;
33Deallocate cur_Inserted;

我简单测试了一下,效果还行,支持批量添加和批量修改。

文章来源: http://www.cnblogs.com/luotong/

Tags:自动 维护 路径

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接