求一触发器
--一记录bom树的表，主要字段，父层编码，子层编层
--求一触发器，在删除中间任一层时，连同该层所有的子层一起删除！
--如下测试数据，在执行delete aa where p_id = 'c001'时，将最后面的三条记录一起删除！
create table aa(p_id varchar(10), c_id varchar(10))
insert aa
select 'a001','b001'
union
select 'b001','c001'
union
select 'c001','d001'
union
select 'c001','f001'
union
select 'f001','g001'
问题点数：50、回复次数：3Top
1 楼mschen（Co-ok）回复于 2004-12-03 23:34:23 得分 0
--这个可以吗?
create trigger tri_aa
on aa from delete
as
delete * from aa
where p_id in (select c_id from deleted)Top
2 楼mschen（Co-ok）回复于 2004-12-03 23:36:17 得分 20
--改正一下
create trigger tri_aa
on aa for delete
as
delete from aa
where p_id in (select c_id from deleted)
--测试成功
/*p_id c_id
---------- ----------
a001 b001
b001 c001
（所影响的行数为 2 行）
*/
Top
3 楼zjcxc（邹建）回复于 2004-12-04 11:47:47 得分 30
--测试数据
create table aa(p_id varchar(10), c_id varchar(10))
insert aa select 'a001','b001'
union all select 'b001','c001'
union all select 'c001','d001'
union all select 'c001','f001'
union all select 'f001','g001'
go
--处理的触发器
create trigger tr_delete on aa
for delete
as
if @@rowcount=0 return
declare @l int
set @l=0
select a.c_id,level=@l into #t
from aa a,deleted d
where a.p_id=d.c_id
while @@rowcount>0
begin
set @l=@l+1
insert #t select a.c_id,level=@l
from aa a,#t d
where a.p_id=d.c_id and d.level=@l-1
end
delete a from aa a,#t b where a.c_id=b.c_id
go
--删除
delete from aa where p_id='b001'
--显示删除结果
select * from aa
go
--删除测试
drop table aa
/*--测试结果
p_id c_id
---------- ----------
a001 b001
（所影响的行数为 1 行）
--*/Top
