表结构:news:
====================
ID Title
1 test
2 test
====================reply:
==========================
ID newsID content
1 2 xxx
2 2 xxx
3 2 xxx
4 1 xxx
5 1 xxx
==========================我在表news建立了一个DELETE触发器,删除news记录时自动删掉回复表(reply)中与之ID关联的数据.
触发器是这样写的CREATE TRIGGER [DeleteNews] ON news
FOR DELETE
AS
DECLARE @intNewsID INTIf @@ROWCOUNT > 0
BEGIN
SELECT @intNewsID = id FROM DELETED
DELETE FROM reply WHERE newsID = @intNewsID
END现在的问题是: 如果用DELETE FROM news WHERE id =数字,则触发器正常.
但使用批量删除的话,例如DELETE FROM news WHERE id IN(1,2,3),触发器就只触发一次....
请问除了在程序中用数组FOR NEXT一个一个的来删之外,还有别的办法吗?
====================
ID Title
1 test
2 test
====================reply:
==========================
ID newsID content
1 2 xxx
2 2 xxx
3 2 xxx
4 1 xxx
5 1 xxx
==========================我在表news建立了一个DELETE触发器,删除news记录时自动删掉回复表(reply)中与之ID关联的数据.
触发器是这样写的CREATE TRIGGER [DeleteNews] ON news
FOR DELETE
AS
DECLARE @intNewsID INTIf @@ROWCOUNT > 0
BEGIN
SELECT @intNewsID = id FROM DELETED
DELETE FROM reply WHERE newsID = @intNewsID
END现在的问题是: 如果用DELETE FROM news WHERE id =数字,则触发器正常.
但使用批量删除的话,例如DELETE FROM news WHERE id IN(1,2,3),触发器就只触发一次....
请问除了在程序中用数组FOR NEXT一个一个的来删之外,还有别的办法吗?
这句话只能取出deleted中的最后的一条记录
If @@ROWCOUNT > 0
BEGIN
delete reply from reply a,deleted b where a.id=b.id
END
FOR DELETE
AS
DECLARE @intNewsID INTIf @@ROWCOUNT > 0
BEGIN
DELETE FROM reply WHERE newsID IN (SELECT id FROM DELETED)
END
FOR DELETE
AS
If @@ROWCOUNT > 0
BEGIN
DELETE FROM reply WHERE newsID IN (SELECT id FROM DELETED)
END
FOR DELETE
AS
--If @@ROWCOUNT > 0
BEGIN
DELETE FROM reply WHERE newsID IN (SELECT id FROM DELETED)
END--为什么一定要判定一下,我觉得可以不要