use test go create table t1(id int ,name Nvarchar(2),parentid int) insert t1 select 1,'A',0 insert t1 select 2,'A',1 insert t1 select 3,'A',2go create trigger del_t1 on t1 for delete as begin declare @i int declare @t table(ID int,ParentID int,lev int) set @i=0 insert @t select t1.ID,t1.ParentID,@i from deleted d join t1 on d.ID=t1.parentid if @@rowcount>0 begin set @i=@i+1 insert @t select a.ID,a.parentid,@i from t1 a join @t t on a.parentid=t.ID and t.lev=@i-1 end delete a from t1 a join @t t on a.ID=t.ID end go --测试 delete t1 where ID=2 goselect * from t1 -- id name parentid ----------- ---- ----------- 1 A 0(所影响的行数为 1 行)
go
create table t1(id int ,name Nvarchar(2),parentid int)
insert t1 select 1,'A',0
insert t1 select 2,'A',1
insert t1 select 3,'A',2go
create trigger del_t1 on t1
for delete
as
begin
declare @i int
declare @t table(ID int,ParentID int,lev int)
set @i=0
insert @t
select t1.ID,t1.ParentID,@i from deleted d join t1 on d.ID=t1.parentid
if @@rowcount>0
begin
set @i=@i+1
insert @t
select
a.ID,a.parentid,@i
from
t1 a
join
@t t on a.parentid=t.ID and t.lev=@i-1
end
delete a from t1 a join @t t on a.ID=t.ID
end
go
--测试
delete t1 where ID=2
goselect * from t1
--
id name parentid
----------- ---- -----------
1 A 0(所影响的行数为 1 行)