select * into #temp from deletedWHILE EXISTS (
SELECT id FROM table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)
)
INSERT #temp
SELECT id FROM table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)delete table1 where id in (select id from #temp)DROP Table #temp
SELECT id FROM table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)
)
INSERT #temp
SELECT id FROM table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)delete table1 where id in (select id from #temp)DROP Table #temp
SELECT id FROM table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)
)
INSERT #temp
SELECT id FROM table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)delete table1 where id in (select id from #temp)DROP Table #temp
CREATE TRIGGER TRIGGERNAME ON DOB.TABLE1
AS
语句做法:把要删除的一条记录写入临时表里,再从表中查是否存在下属,如果有把ID插入到临时表里,再继续循环往下找,直到表中没有属于他们的下属
1.先将deleted表中的数据填入临时表#temp:
select pid, id into #temp from deleted2.最主要的是下面这个循环:
WHILE EXISTS (
SELECT id FROM table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)
) --判断是否有符合条件的记录在table1中INSERT #temp
SELECT pid, id FROM table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp) --将符合条件的记录添加进#temp3.将#temp中记录的id对应的table1中的记录删除
delete table1 where id in (select id from #temp)4.将临时表丢弃。
select * into #temp from deleted
最好改成
select ID into #temp from deleted
FOR DELETE
ASselect * into #temp from my_table1WHILE EXISTS (
SELECT id FROM my_table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)
)
INSERT #temp
SELECT id FROM my_table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)delete my_table1 where id in (select id from #temp)DROP Table #temp我按照上面代码执行后给出提示:
插入错误: 列名或所提供值的数目与表定义不匹配。
然后我把INSERT #temp注释掉了,能够正常执行,但是我删除表中记录后,其他一些不应该删除的记录也被删除了,请问是什么原因呢???小弟是新手,各位大哥不要嫌我烦啊~~~~
FOR DELETE
ASselect Id into #temp from my_table1WHILE EXISTS (
SELECT id FROM my_table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)
)
INSERT #temp
SELECT id FROM my_table1
WHERE pid IN (SELECT ID FROM #temp)
AND id NOT IN (SELECT ID FROM #temp)delete my_table1 where id in (select id from #temp)
DROP Table #temp
go
插入错误: 列名或所提供值的数目与表定义不匹配。
插入错误: 列名或所提供值的数目与表定义不匹配。
插入错误: 列名或所提供值的数目与表定义不匹配。
CREATE TRIGGER TRIGGERNAME ON DBO.my_table1
FOR DELETE
ASdeclare @tblTemp table(ID的定义类型/*如char(20)*/ NOT NULL)insert into @tblTemp select Id from deletedWHILE EXISTS (
SELECT id FROM my_table1
WHERE pid IN (SELECT ID FROM @tblTemp)
AND id NOT IN (SELECT ID FROM @tblTemp)
)
INSERT into @tblTemp
SELECT id FROM my_table1
WHERE pid IN (SELECT ID FROM @tblTemp)
AND id NOT IN (SELECT ID FROM @tblTemp)delete my_table1 where id in (select id from @tblTemp)
go