34,588
社区成员
发帖
与我相关
我的任务
分享
ALTER TRIGGER [dbo].[tr_updateLevelAndPath] ON [dbo].[department]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF ( EXISTS ( SELECT 1
FROM Inserted )
AND NOT EXISTS ( SELECT 1
FROM Deleted ) --此段代表新增加
)
OR ( EXISTS ( SELECT 1
FROM Deleted )
AND NOT EXISTS ( SELECT 1
FROM Inserted ) --此段代表删除
)
OR EXISTS ( SELECT 1
FROM Inserted
INNER JOIN Deleted ON Deleted.ID = Inserted.ID
WHERE Inserted.pid <> Deleted.pid
OR Inserted.department <> Deleted.department ) --修改了PID或者部门名称
BEGIN
WITH cet
AS ( SELECT ID ,
department ,
Leader ,
isProductionDepartment ,
pid ,
0 AS [level] ,
CONVERT(VARCHAR(1000), RIGHT('000000'
+ CONVERT(VARCHAR(20), ROW_NUMBER() OVER ( ORDER BY department )),
6)) AS PATH
FROM dbo.department
WHERE pid IS NULL
OR pid = 0
UNION ALL
SELECT d.ID ,
d.department ,
d.Leader ,
d.isProductionDepartment ,
d.pid ,
cet.[level] + 1 ,
CONVERT(VARCHAR(1000), cet.PATH + '-'
+ RIGHT('000000'
+ CONVERT(VARCHAR(20), ROW_NUMBER() OVER ( ORDER BY d.department )),
6)) AS PATH
FROM dbo.department d
INNER JOIN cet ON cet.ID = d.pid
)
SELECT * ,
( SELECT COUNT(*)
FROM dbo.department
WHERE pid = cet.ID
) AS childCount
INTO #temp
FROM cet;
UPDATE d
SET d.[Level] = #temp.[level] ,
d.[Path] = #temp.[PATH] ,
d.childCount = #temp.childCount
FROM dbo.department d
INNER JOIN #temp ON d.ID = #temp.ID;
END;
END;
USE tempdb
GO
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a
GO
CREATE TABLE a(
id INT,
pid INT,
dname NVARCHAR(10),
theValue INT
)
GO
SET NOCOUNT ON
INSERT INTO a(id,pid,dname) VALUES(1,0,'工程部')
INSERT INTO a(id,pid,dname) VALUES(2,0,'财务部')
INSERT INTO a(id,pid,dname) VALUES(3,1,'工程一班')
INSERT INTO a(id,pid,dname) VALUES(4,1,'工程二班')
INSERT INTO a(id,pid,dname) VALUES(5,3,'工程一班2组')
--将工程部以所有 theValue 更新为 8
;WITH cte AS (
SELECT * FROM a WHERE a.dname='工程部'
UNION ALL
SELECT a.* FROM a INNER JOIN cte ON a.pid=cte.id
)
--SELECT * FROM cte;
UPDATE a
SET theValue=8
FROM cte
WHERE a.id=cte.id
SELECT * FROM a
/*
id pid dname theValue
----------- ----------- ---------- -----------
1 0 工程部 8
2 0 财务部 NULL
3 1 工程一班 8
4 1 工程二班 8
5 3 工程一班2组 8
*/
如果是 2 , 建议用存储过程来完成。
当然, 你得先说清楚你的需求, 大家才方便帮你。