高手进来看看 关于触发器触发删除下级时出现的问题

[wangm_521]

我的一个表为[pro_categories]
为了实现删除节点时触发删除下级，创建触发器为：
CREATE TRIGGER [dbo].[DeletePcChild] ON [dbo].[pro_categories]
FOR DELETE
AS
BEGIN
DELETE FROM pro_categories WHERE pcParentID IN(
SELECT pcID from deleted )
END
比如说有节点AAA，AAA下面有子节点BBB，BBB下面有子节点CCC，为什么删除AAA时只能删除BBB，不能同时删除CCC？？
也就是说只能触发下级的删除，删除BBB时为什么不能连带触发删除CCC呢？
求高手解答！！

[-晴天]

CREATE TRIGGER [dbo].[DeletePcChild] ON [dbo].[pro_categories]
FOR DELETE
AS
BEGIN   
;with cte as(
select pcid from pro_categories where pcParentid in(select pcID from deleted)
union all
select a.pcid from pro_categories a inner join cte b on a.pcParentid=b.pcid
)delete from pro_categories where pcid in(select pcid from cte) 
end

[-晴天]

create table pro_categories(pcid int,pcparentid int)
insert into pro_categories select 1,0 union all select 2,1 union all select 3,1
union all select 4,3 union all select 5,4 union all select 6,2 union all select 7,5
go
CREATE TRIGGER [dbo].[DeletePcChild] ON [dbo].[pro_categories]
FOR DELETE
AS
BEGIN   
;with cte as(
select pcid from pro_categories where pcParentid in(select pcID from deleted)
union all
select a.pcid from pro_categories a inner join cte b on a.pcParentid=b.pcid
)delete from pro_categories where pcid in(select pcid from cte) 
end
go
delete pro_categories where pcid=3
select * from pro_categories
/*
pcid        pcparentid
----------- -----------
1           0
2           1
6           2

(3 行受影响)
*/
go
drop table pro_categories

[wangm_521]

[Quote=引用 1 楼 qianjin036a 的回复:]
SQL code
CREATE TRIGGER [dbo].[DeletePcChild] ON [dbo].[pro_categories]
FOR DELETE
AS
BEGIN
;with cte as(
select pcid from pro_categories where pcParentid in(select pcID from deleted)
union ……
[/Quote]

多谢高手 OK啦

[X_0]

ALTER   DATABASE   dbname   ---这里是数据库名字
SET   RECURSIVE_TRIGGERS   on 

在你的查询分析器中执行下这个，或者是在企业管理器中数据库的属性中把触发器递归选项设为TRUE
默认的是禁止直接递归触发器的，不过不建议你这样做，递归的最大层数是32，或许会对你产生异常，改为用代码处理一起把需要删除的删除完