declare @temp table (id char(8))insert @temp values ('2')while exists ( select id from table where parentid in (select id from @temp) and id not in (select id from @temp) ) insert @temp select id from table where parentid in (select id from @temp) and id not in (select id from @temp) ---把所有子节点插入@tempdelete from table tempTab where parentid in (select id from @temp)
这样吧: 使用一个触发器,里面这样做: IF EXISTS (SELECT name FROM sysobjects WHERE name = N'<trigger_name, sysname, trig_test>' AND type = 'TR') DROP TRIGGER <trigger_name, sysname, trig_test> GOCREATE TRIGGER <trigger_name, sysname, trig_test> ON <table_name, sysname, pubs.dbo.sales> FOR DELETE AS BEGIN declare @F int,@Id1 int,@Id2 int select @Id1=id from deleted select @Id2=id from table_name where parentid=@Id1 set @F=case when @Id2 is null then 0 else 1 end while @F=1 begin set @Id1=Id2 deleted from table_name where id=@Id2 select @Id2=id from table_name where parentid=@Id1 set @F=case when @Id2 is null then 0 else 1 end end END GO
select id from table
where parentid in (select id from @temp)
and id not in (select id from @temp)
)
insert @temp
select id from table
where parentid in (select id from @temp)
and id not in (select id from @temp)
---把所有子节点插入@tempdelete from table tempTab
where parentid in (select id from @temp)
使用一个触发器,里面这样做:
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<trigger_name, sysname, trig_test>'
AND type = 'TR')
DROP TRIGGER <trigger_name, sysname, trig_test>
GOCREATE TRIGGER <trigger_name, sysname, trig_test>
ON <table_name, sysname, pubs.dbo.sales>
FOR DELETE
AS
BEGIN
declare @F int,@Id1 int,@Id2 int
select @Id1=id from deleted
select @Id2=id from table_name where parentid=@Id1
set @F=case when @Id2 is null then 0 else 1 end
while @F=1
begin
set @Id1=Id2
deleted from table_name where id=@Id2
select @Id2=id from table_name where parentid=@Id1
set @F=case when @Id2 is null then 0 else 1 end
end
END
GO