SQLServer触发器递归更新

起因：有一个组表，是树型结构，结构如下：

CREATE TABLE CFG_TEAM(
	TEAM_ID INT,TEAM_NAME VARCHAR(50),PATHS VARCAHR(4000),--根据当前层次自动计算更新
	PARENT_ID INT)

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)

方法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,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 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!