sql server的触发器的问题，高分求购！

chnking 2002-06-30 10:51:47

我有一张表，表名为“test”，用来存放组树状结构的数据（结果要在treeview控件中显示)，结构如下：
id int /*主键,表示这个节点的代码*/
parnt int /*表示这个节点的父节点的代码*/
content char(50) /*这个节点要显示的内容*/

现在我要想做一个触发器，在删除了一个节点的时候，把这个节点下面的子节点都统统的删除了，触发器（“kent”）设计如下：
CREATE TRIGGER kent ON [dbo].[test]
FOR DELETE
AS
begin
declare @id int
select @id=(select id from deleted)
delete from test where parent = @id
end

这个触发器的结果是：删除一个节点的时候，触发了这个触发器，这个触发器会删除以这个节点为父节点的那些节点，可是子节点的子节点没有删除。
感觉是外部删除了一个节点会触发这个触发器，可是触发器自己删除的节点却不再触发触发器本身了。

各位，有没有办法帮我解决这个问题？

...全文

157 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

chnking 2002-06-30

打赏
举报

to luojx(大鱼儿)
能不能说的具体点啊？

luojx 2002-06-30

打赏
举报

试试让触发器执行存储过程，
存储过程去执行删除操作
存储过程可以继续调用本身

chnking 2002-06-30

打赏
举报

CREATE TRIGGER kent2 ON [dbo].[test]
FOR DELETE
AS
BEGIN

SELECT ID INTO #TEMP FROM DELETED

WHILE 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

chnking 2002-06-30

打赏
举报

CREATE TRIGGER kent2 ON [dbo].[test]
FOR DELETE
AS
BEGIN

SELECT ID INTO #TEMP FROM DELETED

WHILE 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

CSDNM 2002-06-30

打赏
举报

我的全写错了
所有的if改成id

OpenVMS 2002-06-30

打赏
举报

CREATE TRIGGER kent ON [dbo].[test]
FOR DELETE
AS
BEGIN

SELECT ID INTO #TEMP FROM DELETED

WHILE 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

j9988 2002-06-30

打赏
举报

CREATE TRIGGER kent1 ON [dbo].[test]
FOR DELETE
AS
begin

declare @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)
begin

insert 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
break

end

delete from test where id in (select id from #temp1)
drop table #temp1

end

CSDNM 2002-06-30

打赏
举报

CREATE TRIGGER kent ON [dbo].[test]
FOR DELETE
AS
begin

select 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