补充一下那在同一个表中怎么做呢?
比如类别表:
类型编号 类型名称 父类编号 类型等级
1 type1 1 1
11 type11 1 2
12 type12 1 2
21 type21 21 3
22 type22 22 3
23 type22 23 3
比如类别表:
类型编号 类型名称 父类编号 类型等级
1 type1 1 1
11 type11 1 2
12 type12 1 2
21 type21 21 3
22 type22 22 3
23 type22 23 3
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([类型编号] int,[类型名称] varchar(6),[父类编号] int,[类型等级] int)
insert [tb]
select 1,'type1',0,1 union all
select 11,'type11',1,2 union all
select 12,'type12',1,2 union all
select 21,'type21',11,3 union all
select 22,'type22',11,3 union all
select 23,'type22',21,4--创建触发器
create trigger tri_tb on tb for delete
as
with cte as
(
select a.[类型编号],a.[父类编号] from tb a join deleted d on a.[父类编号]=d.[类型编号]
union all
select a.[类型编号],a.[父类编号] from tb a join cte c on a.[父类编号]=c.[类型编号]
)
delete a from tb a join cte c on a.[类型编号]=c.[类型编号]
go
--测试:
delete tb where [类型编号]=11select * from [tb]
--测试结果:
/*
类型编号 类型名称 父类编号 类型等级
----------- ------ ----------- -----------
1 type1 0 1
12 type12 1 2(2 row(s) affected)
*/
drop table tb