＋设为首页 ＋添加到收藏夹
我现在有个表叫nClass，用来生成树形目录，里面有nClassId …… ParentId几个字段。ParentId里面储存的就是这条记录的上级记录的nClassId，顶级记录的ParentId为零。现在我想在DELETE一条记录的同时删除它下属的所有的记录。但我发现触发器只能被触发一次，被触发器删除的记录就不能再次激活触发器了，请问想要循环被触发的触发器怎样设计啊？
我写的的触发器是这样的：
CREATE TRIGGER nClass_ParentId_Delete ON dbo.nClass
FOR DELETE
AS
DELETE nClass FROM deleted WHERE nClass.ParentId=deleted.nClassId
请各位大虾怎样改啊？
谢谢了
可以打开数据库的触发器“直接”递归功能：
ALTER DATABASE 数据库名 SET RECURSIVE_TRIGGERS ON
这样就能递归了。
但是，递归的层数不能超过32层。
1 root 0
2 child1 1
3 child2 1
4 child3 2
declare @Temp table (id int)
insert @Temp values(@id)
while exists (
select id from tablename
where id not in (select id from @Temp)
and fatherid in (select id from @Temp)
)
insert @Temp
select id from tablename
where id not in (select id from @Temp)
and fatherid in (select id from @Temp)
delete from tablename
where id in (select id from @Temp)
