树中删除父节点同时删除它下面的字节点

[Accelerator]

例如有表如下：
ID PID Name


如何删除父节点同时删除它下面的字节点

谢谢

[caixia615]

贴点数据看下

[splory]

--示例
create table emp(
eid int primary key,
ename varchar(20),
sal money,
reportto int references emp(eid) --该员工的直接上级
)
--drop table emp
insert into emp select 1001,'rain',1000,null
union select 1002,'ann',3000,1001
union select 1003,'lopez',2000,1001
union select 1004,'nakata',3000,1002
union select 1005,'tae',1500,1004
union select 1006,'raul',900,1004
union select 1007,'owen',15000,1006

/*实现级联查询 例如，提供一个员工编号，能够查询出该员工的下级和间接下级的信息。*/
create proc p2 @eid int
as
begin
declare @re table(eid int,level int)
declare @l int
set @l=0
insert @re select @eid,@l
while @@rowcount>0
--全局变量@@rowcount，记录上次操作影响的行数
begin
set @l=@l+1
insert @re select a.eid,@l from emp as a,@re as b where a.reportto=b.eid and b.level=@l-1
--循环将当前记录的直接下级插入@re
end
select a.* from @re as b inner join emp as a on a.eid = b.eid
end
--drop proc p2
exec p2 1004

对结果集进行删除操作就成了

[zsl5305256]

用delete触发器,并设为递归调用!
exec sp_configure 'nested triggers','1'
go
Create trigger deletemenuName
on dbo.fmenu
for delete
as
if @@rowcount>0
begin
delete fmenu from deleted where fmenu.parentid=deleted.menuid
end


GO