删除处理触发器(同步删除被删除节点的所有子节点)

--删除处理触发器(同步删除被删除节点的所有子节点)

create trigger tr_deletenode on tbc

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 tbc a,deleted d

where a.pid=d.id

while @@rowcount>0

begin

    set @level=@level+1

    insert @t select a.id,@level

    from tbc a,@t b

    where a.pid=b.id

        and b.level=@level-1

end

delete a

from tbc a,@t b

where a.id=b.id

go

 

--删除

delete from tbc where id in(2)

select * from tbc