起因:有一个组表,是树型结构,结构如下:
CREATE TABLE CFG_TEAM(
TEAM_ID INT,
TEAM_NAME VARCHAR(50),
PATHS VARCAHR(4000), --根据当前层次自动计算更新
PARENT_ID INT)
这个paths有点类似文件目录的意思,是父级节点paths+'.'+本级节点ID,当父级节点ID为0时,paths='' 还有一种情况是当某个有子节点的节点变更父节点时,该节点的Paths和所有子节点的paths都要依次变更(相当于把一个文件夹移动位置)
paths主要目的是查询时可以用like,而且不需要使用 with cte递归。
数据表结果举例:
INSERT INTO CFG_TEAM VALUES (1, 'A', '', 0)
INSERT INTO CFG_TEAM VALUES (2, 'AA', '1.2', 1)
INSERT INTO CFG_TEAM VALUES (3, 'AB', '1.3', 1)
INSERT INTO CFG_TEAM VALUES (4, 'AAA', '1.2.4', 2)
INSERT INTO CFG_TEAM VALUES (5, 'B', '', 0)
INSERT INTO CFG_TEAM VALUES (6, 'BA', '5.6', 5)
INSERT INTO CFG_TEAM VALUES (7, 'AAAA', '1.2.4.7', 4)
INSERT INTO CFG_TEAM VALUES (8, 'AAAAA', '1.2.4.7.8', 7)
INSERT INTO CFG_TEAM VALUES (9, 'AAAAAA', '1.2.4.7.8.9', 8)
问题:如何更新这个paths字段呢?
方法1:使用函数
update cfg_team set paths = fn_team_paths(team_id)
where team_id = 3 and parent_id = 1
方法2:使用触发器,呵呵,首先这个表是基础性的表,不需要频繁更新,所以触发器的性能、效率等都不是问题,问题关键是自动更新,insert数据时根据parent_id计算当前的paths、update数据时计算当前paths和parent_id为当前team_id的记录,这便是递归更新,实验了很久下面直接给出代码,能不能领悟看运气吧
--开启触发器递归
ALTER DATABASE [DATABASE]
SET RECURSIVE_TRIGGERS ON
GO
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'TEAM_INSERT' AND TYPE = 'TR')
DROP TRIGGER TEAM_INSERT
GO
--创建INSERT触发器
CREATE TRIGGER TEAM_INSERT
ON CFG_TEAM
AFTER INSERT
AS
--插入新记录时自动更新该记录的PATHS值
UPDATE T SET PATHS = CASE
WHEN ISNULL(P.PATHS, '') = '' THEN LTRIM(STR(P.TEAM_ID)) + '.' + LTRIM(STR(T.TEAM_ID))
ELSE P.PATHS + '.' + LTRIM(STR(T.TEAM_ID))
END
FROM CFG_TEAM T, INSERTED I, CFG_TEAM P
WHERE T.TEAM_ID = I.TEAM_ID AND I.PARENT_ID = P.TEAM_ID AND I.PARENT_ID <> 0
GO
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = 'TEAM_UPDATE' AND TYPE = 'TR')
DROP TRIGGER TEAM_UPDATE
GO
--创建UPDATE触发器
CREATE TRIGGER TEAM_UPDATE
ON CFG_TEAM
AFTER UPDATE
AS
--1、若有记录PARENT_ID值变更
IF UPDATE(PARENT_ID) AND EXISTS(SELECT * FROM INSERTED)
BEGIN
--2、更新变更记录的PATHS值(PARENT_ID == 0)
UPDATE T SET PATHS = ''
FROM CFG_TEAM T, INSERTED I
WHERE T.TEAM_ID = I.TEAM_ID AND T.PARENT_ID = 0
--3、更新变更记录的PATHS值(PARENT_ID <> 0)
UPDATE T SET PATHS = CASE
WHEN ISNULL(P.PATHS, '') = '' THEN LTRIM(STR(P.TEAM_ID)) + '.' + LTRIM(STR(T.TEAM_ID))
ELSE P.PATHS + '.' + LTRIM(STR(T.TEAM_ID))
END
FROM CFG_TEAM T, INSERTED I, CFG_TEAM P
WHERE T.TEAM_ID = I.TEAM_ID AND I.PARENT_ID = P.TEAM_ID AND I.PARENT_ID <> 0
--4、刷新变更下级记录PARENT_ID,让系统自动递归更新
UPDATE T SET T.PARENT_ID = T.PARENT_ID
FROM CFG_TEAM T, INSERTED I
WHERE T.PARENT_ID = I.TEAM_ID
END