CREATE TRIGGER kent ON [dbo].[test] FOR DELETE AS beginselect id into #Temp from test where parent in (select if from deleted)while exists (select * from test where parent in (select if from #Temp) and id not in (select if from #Temp)) insert #Temp select id from test where parent in (select if from #Temp) and id not in (select if from #Temp)delete test where id in (select if from #Temp) end go
CREATE TRIGGER kent1 ON [dbo].[test] FOR DELETE AS begindeclare @id int select @id=(select id from deleted) create table #temp1( id int, /*主键,表示这个节点的代码*/ parent int /*表示这个节点的父节点的代码*/ ) insert into #temp1(id,parent) (select id,parent from test where parent=@id )while (1=1) begininsert into #temp1(id,parent) (select test.id,test.parent from test,#temp1 where test.id not in (select id from #temp1) and test.parent=#temp1.id )if @@rowcount=0 breakenddelete from test where id in (select id from #temp1) drop table #temp1end
CREATE TRIGGER kent ON [dbo].[test] FOR DELETE AS BEGINSELECT ID INTO #TEMP FROM DELETEDWHILE EXISTS ( SELECT ID FROM test WHERE parent IN (SELECT ID FROM #TEMP) AND ID NOT IN (SELECT ID FROM #TEMP) ) INSERT #TEMP SELECT ID FROM test WHERE parent IN (SELECT ID FROM #TEMP) AND ID NOT IN (SELECT ID FROM #TEMP)DELETE test WHERE ID IN (SELECT ID FROM #TEMP) DROP TABLE #TEMP END
我的全写错了 所有的if改成id
CREATE TRIGGER kent2 ON [dbo].[test] FOR DELETE AS BEGINSELECT ID INTO #TEMP FROM DELETEDWHILE EXISTS ( SELECT ID FROM test WHERE parent IN (SELECT ID FROM #TEMP) AND ID NOT IN (SELECT ID FROM #TEMP) ) begin INSERT #TEMP SELECT ID FROM test WHERE parent IN (SELECT ID FROM #TEMP) AND ID NOT IN (SELECT ID FROM #TEMP)DELETE test WHERE ID IN (SELECT ID FROM #TEMP) end DROP TABLE #TEMP END
CREATE TRIGGER kent2 ON [dbo].[test] FOR DELETE AS BEGINSELECT ID INTO #TEMP FROM DELETEDWHILE EXISTS ( SELECT ID FROM test WHERE parent IN (SELECT ID FROM #TEMP) ) begin INSERT #TEMP SELECT ID FROM test WHERE parent IN (SELECT ID FROM #TEMP)DELETE test WHERE ID IN (SELECT ID FROM #TEMP) end DROP TABLE #TEMP END
FOR DELETE
AS
beginselect id into #Temp from test where parent in (select if from deleted)while exists (select * from test where parent in (select if from #Temp) and id not in (select if from #Temp))
insert #Temp select id from test where parent in (select if from #Temp) and id not in (select if from #Temp)delete test where id in (select if from #Temp)
end
go
FOR DELETE
AS
begindeclare @id int
select @id=(select id from deleted)
create table #temp1(
id int, /*主键,表示这个节点的代码*/
parent int /*表示这个节点的父节点的代码*/
)
insert into #temp1(id,parent) (select id,parent from test where parent=@id )while (1=1)
begininsert into #temp1(id,parent) (select test.id,test.parent from test,#temp1 where test.id not in (select id from #temp1) and test.parent=#temp1.id )if @@rowcount=0
breakenddelete from test where id in (select id from #temp1)
drop table #temp1end
FOR DELETE
AS
BEGINSELECT ID INTO #TEMP FROM DELETEDWHILE EXISTS (
SELECT ID FROM test
WHERE parent IN (SELECT ID FROM #TEMP)
AND ID NOT IN (SELECT ID FROM #TEMP)
)
INSERT #TEMP
SELECT ID FROM test
WHERE parent IN (SELECT ID FROM #TEMP)
AND ID NOT IN (SELECT ID FROM #TEMP)DELETE test
WHERE ID IN (SELECT ID FROM #TEMP)
DROP TABLE #TEMP
END
所有的if改成id
FOR DELETE
AS
BEGINSELECT ID INTO #TEMP FROM DELETEDWHILE EXISTS (
SELECT ID FROM test
WHERE parent IN (SELECT ID FROM #TEMP)
AND ID NOT IN (SELECT ID FROM #TEMP)
)
begin
INSERT #TEMP
SELECT ID FROM test
WHERE parent IN (SELECT ID FROM #TEMP)
AND ID NOT IN (SELECT ID FROM #TEMP)DELETE test
WHERE ID IN (SELECT ID FROM #TEMP)
end
DROP TABLE #TEMP
END
FOR DELETE
AS
BEGINSELECT ID INTO #TEMP FROM DELETEDWHILE EXISTS (
SELECT ID FROM test
WHERE parent IN (SELECT ID FROM #TEMP)
)
begin
INSERT #TEMP
SELECT ID FROM test
WHERE parent IN (SELECT ID FROM #TEMP)DELETE test
WHERE ID IN (SELECT ID FROM #TEMP)
end
DROP TABLE #TEMP
END