数据库表：
字段有：类别名 父类别名
要求删除这样的记录：
告诉你类别名，删除这条类别和所有子类别~~~~~~~
怎么写？
---------------------------------------------------------------
declare @idstr int,@lastlen in,
select @idstr=@InputDelID ---如果要做成存储过程。这个就是传入参数
select @lastlen=0
while len(@idstr)<>@lastlen
begin
select @lastlen=len(@idstr)
select @idstr=@idstr+','+cast(id as varchar) from table where fatherid in @idstr
end
delect from table where id in @idstr
---------------------------------------------------------------
----用递归触发器的解决方法
drop table test
go
create table test(id_p int,id_s int)
go
insert into test values(1,2)
insert into test values(2,3)
insert into test values(2,4)
insert into test values(3,5)
insert into test values(3,7)
insert into test values(3,9)
insert into test values(4,6)
insert into test values(4,8)
insert into test values(6,11)
go
----设置触发器可递归。但最大只能嵌套32层
EXEC sp_configure 'nested triggers', '1' RECONFIGURE WITH OVERRIDE
alter database chiff SET RECURSIVE_TRIGGERS on
go
create trigger tr_test_d on test for delete as
if @@rowcount > 0
delete test where id_p in(select id_s from deleted)
go
delete test where id_p = 2
select * from test
----效率更高一点
create proc p_test
@deletevalue int
as
declare @table table(did int)
insert into @table values(@deletevalue)
while 1=1
begin
insert into @table
select id_s
from test where id_p in(select did from @table) and
id_s not in(select did from @table)
if @@rowcount = 0
break
end
delete test where id_s in(select did from @table)
go