存储过程:create proc proc_name
@id int
asdeclare @temp table (id int)insert @temp values (@id)while exists (
select autoid from tablename
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)
)
insert @temp
select autoid from tablename
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)delete tablename
where autoid in (select id from @temp)go
@id int
asdeclare @temp table (id int)insert @temp values (@id)while exists (
select autoid from tablename
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)
)
insert @temp
select autoid from tablename
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)delete tablename
where autoid in (select id from @temp)go
如果只删除子节点,不会自动再调用触发器删除删除下一层节点。触发器代码与杨的类似,只是将@id换成删除表中的id而已。
一个外键表rational(treeid[关联到tree表的id],fatherid[关联到tree表的id])
一个tree for delete的触发器这样你在删除tree表记录时,就可以删除rational表的相应记录了
INSTEAD OF DELETE
AS
delete from rational where (treeid in (select id from deleted)) or (fatherid in (select id from deleted))
把autoid、 fatherid 做一个表的主外兼连接。然后选中级连删除就可以了。
我是分三步实现的:第一:连接库,先删除autoid或者fatherid为某一指定值的,关闭库;
第二:连接库,把所有的autoid读出来存为一个数组,关闭库;
第三:连接库,删除所有fatherid not in (数组),关闭库。如果三步都不出错的话(谁敢做这样的保证?),就
这样总算可以解燃眉之急了,权益之计。望各位大虾指教出更为合理的办法。
你的做法这么麻烦,而且也看不出你如何保证不留垃圾在里面,好像还是只能删除两层。
for delete
asdeclare @temp table (id int)
declare @id int
select @id=autoid from deletedinsert @temp values (@id)while exists (
select autoid from tree
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)
)
insert @temp
select autoid from tree
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)delete tree
where autoid in (select id from @temp)go
(autoid int,name varchar(10),fatherid int)insert into tree values(1,'a1',0)
insert into tree values(2,'a2',0)
insert into tree values(3,'a3',0)insert into tree values(4,'a11',1)
insert into tree values(5,'a12',1)
insert into tree values(6,'a13',1)insert into tree values(7,'a21',2)
insert into tree values(8,'a22',2)insert into tree values(9,'a111',4)
insert into tree values(10,'a112',4)
insert into tree values(11,'a113',4)insert into tree values(12,'a121',5)
insert into tree values(13,'a122',5)select * from tree order by namedelete from tree where autoid=1select * from tree order by name
if exists(select name from sysobjects where name='tree' and type='u')
drop table tree
create table tree
(autoid int,name varchar(10),fatherid int)insert into tree values(1,'a1',0)
insert into tree values(2,'a2',0)
insert into tree values(3,'a3',0)insert into tree values(4,'a11',1)
insert into tree values(5,'a12',1)
insert into tree values(6,'a13',1)insert into tree values(7,'a21',2)
insert into tree values(8,'a22',2)insert into tree values(9,'a111',4)
insert into tree values(10,'a112',4)
insert into tree values(11,'a113',4)insert into tree values(12,'a121',5)
insert into tree values(13,'a122',5)
-------------------------------------------------------------------------------------
--設要刪除的行autoid=9.你可以把下面的代碼改為存儲過程﹐入口參數為@inautoid
declare @autoid int,
@fatherid int,
@count int,
@inautoid intselect @inautoid=9select @autoid=autoid,@fatherid=fatherid from tree
where autoid=@inautoiddelete tree
where autoid=@inautoidselect @inautoid=@fatheridselect @count=count(*) from tree
where autoid=@inautoidwhile @count<>0
begin
select @count=count(*) from tree
where autoid=@inautoidif @count=0 begin break end
select @autoid=autoid,@fatherid=fatherid from tree
where autoid=@inautoiddelete tree
where autoid=@inautoidselect @inautoid=@fatherid
end
是不是你要的?试试吧.
所以第一步建议存储过程:create proc proc_name
@id int
asdeclare @temp table (id int)insert @temp values (@id)while exists (
select autoid from tablename
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)
)
insert @temp
select autoid from tablename
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)delete tablename
where autoid in (select id from @temp)go
第二步建立触发器
create trigger treedelete on tablename
after delete
as
begin
declare @id int
declare my_cursor cursor for select autoid from deleted
open my_cursor
fetch next from my_cursor into @id
while @@fetch_status = 0
begin
exec proc_name @id
fetch next from my_cursor into @id
end
endgo
然后你做个删除试试
@id int
as declare @temp table (id int)
insert @temp values (@id)
while exists (
select autoid from tablename
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)
)
insert @temp
select autoid from tablename
where fatherid in (select id from @temp)
and autoid not in (select id from @temp)
delete from tablename
where autoid in (select id from @temp)go
create trigger treedelete on tablename
after delete
as
begin
declare @id int
declare my_cursor cursor for select autoid from deleted
open my_cursor
fetch next from my_cursor into @id
while @@fetch_status = 0
begin
exec proc_name @id
fetch next from my_cursor into @id
end
endgo
set recursive_triggers on
go
drop trigger delete_tree
go
create trigger delete_tree on yourtablename after delete
as
begin
if exists (select * from deleted)
delete from yourtablename from deleted where yourtablename.fatherid=deleted.autoid
end
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
select * from tree