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
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
gocreate 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 OVERRIDEalter database chiff SET RECURSIVE_TRIGGERS on
gocreate trigger tr_test_d on test for delete as
if @@rowcount > 0
delete test where id_p in(select id_s from deleted)godelete test where id_p = 2
select * from test
gocreate 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
create proc p_test
@deletevalue int
asdeclare @n int
declare @m intselect @deletevalue as did into #tablewhile 1=1
begin insert into #table
select id_s from test where id_p in(select distinct did from #table)
set @m = @n
set @n = @@rowcount
if @m = @n
break enddelete test where id_s in(select distinct did from #table)
go
@deletevalue int
asdeclare @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
enddelete test where id_s in(select did from @table)
go
(id int)
declare @id int
set @id = 2
insert into #temp values (@id)
while exists (select id from tree where fatherid in (select * from #temp) and id not in (select * from #temp) )
insert into #temp select id from tree where fatherid in (select * from #temp) and id not in (select * from #temp)
delete tree from #temp where tree.id = #temp.id