同步删除被删除节点的所有子节点(BOM节点删除)

最新推荐文章于 2022-06-24 12:13:08 发布

最新推荐文章于 2022-06-24 12:13:08 发布 · 141 阅读

文章标签：

本文介绍了一种使用SQL触发器实现递归删除数据的方法，并展示了如何通过递归公用表表达式（CTE）来查询层级结构数据。首先创建了一个示例表并填充数据，接着定义了触发器用于当特定父节点被删除时，其所有子节点也一同被删除。此外，还提供了一种递归查询方法来获取指定节点的所有子节点。

CREATE TABLE tb(ID int,PID int,Name nvarchar(10))
INSERT tb SELECT 1,NULL,'山东省'
UNION ALL SELECT 2,1 ,'烟台市'
UNION ALL SELECT 4,2 ,'招远市'
UNION ALL SELECT 3,1 ,'青岛市'
UNION ALL SELECT 5,NULL,'四会市'
UNION ALL SELECT 6,5 ,'清远市'
UNION ALL SELECT 7,6 ,'小分市'
GO

--删除处理触发器(同步删除被删除节点的所有子节点)
CREATE TRIGGER tr_DeleteNode ON tb
FOR DELETE
AS
IF @@ROWCOUNT=0 RETURN --如果没有满足删除条件的记录,直接退出
--查找所有被删除节点的子节点
DECLARE @t TABLE(ID int,Level int)
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.ID,@Level
FROM tb a,deleted d
WHERE a.PID=d.ID
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.ID,@Level
FROM tb a,@t b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
DELETE a
FROM tb a,@t b
WHERE a.ID=b.ID
GO

--删除
DELETE FROM tb WHERE ID in(2,3,5)
SELECT * FROM tb
/*--结果
ID PID Name
---------------- ----------------- ----------
1 NULL 山东省
--*/

if object_id('tb') is not null
drop table tb
go

create table tb(id int,name varchar(50),parentId int)
go

insert into tb
select 1 , '工具', 0 union all
select 2 , '工具1', 1 union all
select 3 , '工具2', 1 union all
select 4 , '工具3', 1 union all
select 6 , '工具21', 2 union all
select 7 , '工具311', 3 union all
select 8 , '工具211', 6 union all
select 9 , '系统', 0 union all
select 10, '系统1' , 9

go

-- 2000 写法

if object_id('dbo.f_getchirdNode()') is not null
drop function dbo.f_getchirdNode
go

create function f_getchirdNode(@id int)
returns @t_level table(id varchar(3) , level int)
begin
declare @level int
set @level = 1
insert into @t_level select @id , @level
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.id , @level
from tb a , @t_Level b
where a.parentId = b.id and b.level = @level - 1
end
return
end
go
select * from tb a where exists (select * from f_getchirdNode(1) b where a.id = b.id) -- 查询
delete from tb where exists (select * from f_getchirdNode(1) b where tb.id = b.id) -- 删除
go

-- 2005
declare @id int
set @id = 1
;with t as
(
select * from tb where id = @id
union all
select a.* from tb a,t b where a.parentId = b.id
)
delete from tb where exists (select 1 from t where tb.id = id) -- 删除
--select * fromt --查询
select * from tb

drop table tb