关于“替代触发器”一个小疑问解决方案

关于“替代触发器”一个小疑问

SQL code

--------delete 触发器if exists(select * from sysobjects where name='tri_delCardInfo' and type='TR')drop trigger tri_delCardInfogocreate trigger tri_delCardInfoon cardinstead of deleteasbegin    delete from transInfo where cardNo in (select cardNo from deleted)    delete from card where cardNo in (select cardNo from deleted)end----------------------------------------当执行一下操作时候：--delete from card where cardNO in ('100019','100009')-------------------------------------返回如下结果：-----------------------------------(5 行受影响)------5行受影响可以明白，是删除了transInfo 表中CardNO为‘09’与‘19’的数据。[color=#FF0000]我的问题是:下面为什么有2个（2行受影响）？？？其中肯定有一个是删除了Card表的2行，另一个勒？[/color]------------------(2 行受影响)(2 行受影响)

[解决办法]
一个是触发器中真正删除card 两条记录
一个是delete from card where cardNO in ('100019','100009')语句删除两条记录（虽然被触发器代替了）

在触发器增加SET NOCOUNT ON可以避免这类误解

--------delete 触发器
if exists(select * from sysobjects where name='tri_delCardInfo' and type='TR')
drop trigger tri_delCardInfo
go
create trigger tri_delCardInfo
on card
instead of delete
as
begin
SET NOCOUNT ON;
delete from transInfo where cardNo in (select cardNo from deleted)
delete from card where cardNo in (select cardNo from deleted)
end
GO
-------------------------------------
---当执行一下操作时候：
--delete from card where cardNO in ('100019','100009')
-----------------------------------
--返回如下结果：
(2 行受影响)


[解决办法]

探讨

一个是触发器中真正删除card 两条记录
一个是delete from card where cardNO in ('100019','100009')语句删除两条记录（虽然被触发器代替了）

在触发器增加SET NOCOUNT ON可以避免这类误解

--------delete 触发器
if exists(select * from sysobjects where name='……

[解决办法]
触发器默认情况下是不能嵌套执行的，需要设置sp_configure 下nest trigger选项为true才可以，但是也只能嵌套32层
[解决办法]
一个+
一个

不是两个吗，确认真的看明白了？

查看更多 下一篇