从INSTEAD OF TRIGGER获取ROWCOUNT

问题描述

旧版应用程序在表上执行INSERT操作，而不是使用触发器，然后使用行数进行进一步处理。

我们现在需要选择退出某些INSERT使用 INSTEAD OF INSERT 触发器。

问题是@@ ROWCOUNT仍返回尝试插入的次数。

例如，一个永远不会完成插入的虚拟触发器可能是

  ALTER TRIGGER [dbo]。[trig_ACCOUNT_CREDITS_RunningTotalINSERT] $ [account_credits] 
插入
作为
开始
 –尝试了不开和关
设置了不打折； 
 
-这是分支逻辑的示例，可以确定
-如果不插入INSERT 
，如果1 = 2-不会发生插入（仅示例）
开始
插入dbo.ACCOUNT_CREDITS（COL1，COL2）
从INSERTED 
 END选择$ COL1，COL2 END 
 END 
  

和某些INSERT语句可能是

 -由于COL1的值插入dbo.ACCOUNT_CREDITS（COL1，COL2）值（3，3）
 
-我们假设行数为0，但返回1 
 select @@ ROWCOUNT 
 
-因为COL1的值小于5 
插入dbo.ACCOUNT_CREDITS（COL1，COL2）
 SELECT 1，1 
 union all 
 SELECT 2，2 
 
-我们假设行计数为0，但返回2 
 select @@ ROWCOUNT 
  

我可以解决问题，但令我感到困扰的是我不信任@@ ROWCOUNT。我在SO或其他 other 知识库中找不到关于此问题的参考。这仅仅是 TRIGGERS ARE EVIL吗？

我可以影响@@ ROWCOUNT吗？

解决方案

某些语句可能会在触发器内更改@@ ROWCOUNT。

声明

 选择*来自插入位置COL1< 5 
  

执行并将@@ ROWCOUNT设置为1

输入语句

 设置NOCOUNT ON； 
  

然后

 如果不存在（选择*从插入的COL1中开始
设置NOCOUNT OFF； 
 
插入dbo.ACCOUNT_CREDITS（COL1，COL2）
从INSERTED 
 END 
   
A legacy app does an INSERT on a table with an instead of trigger and subsequently uses the rowcount for further processing.

We now need to opt out of certain INSERTs with the use of an INSTEAD OF INSERT trigger. 

The problem is that @@ROWCOUNT still returns the number of attempted inserts.

For example, a fictitious trigger that will never complete an insert might be
ALTER TRIGGER [dbo].[trig_ACCOUNT_CREDITS_RunningTotalINSERT]
   ON  [dbo].[ACCOUNT_CREDITS] 
   INSTEAD OF INSERT
AS 
BEGIN
 --tried with NOCOUNT ON and OFF
 SET NOCOUNT OFF;

 --This is an example of the branching logic that might determine 
 --whether or not to do the INSERT
IF 1=2 --no insert will ever occur (example only)
BEGIN
    INSERT INTO dbo.ACCOUNT_CREDITS (COL1, COL2)
    SELECT COL1, COL2 from INSERTED
END
END
and some INSERT statements might be
--No rows will be inserted because value of COL1 < 5
INSERT INTO dbo.ACCOUNT_CREDITS (COL1, COL2) VALUES ( 3, 3)

--We would assume row count to be 0, but returns 1
select @@ROWCOUNT

--No rows will be inserted because value of COL1 < 5       
INSERT INTO dbo.ACCOUNT_CREDITS (COL1, COL2) 
SELECT 1, 1
union all
SELECT 2, 2

--We would assume row count to be 0, but returns 2
select @@ROWCOUNT
I can work around the issue, but it bothers me that I can't trust @@ROWCOUNT.  I can find no reference to this issue on SO or those other knowledge banks.  Is this simply a case of TRIGGERS ARE EVIL?
Can I affect @@ROWCOUNT?
 解决方案 
Some statements may change @@ROWCOUNT inside the trigger.
Statement
SELECT * FROM INSERTED WHERE COL1 < 5
executes and set @@ROWCOUNT to 1
Put statement
SET NOCOUNT ON;
then
IF NOT EXISTS (SELECT * FROM INSERTED WHERE COL1 < 5)
BEGIN
    SET NOCOUNT OFF;

    INSERT INTO dbo.ACCOUNT_CREDITS (COL1, COL2)
    SELECT COL1, COL2 from INSERTED
END
                        
这篇关于从INSTEAD OF TRIGGER获取ROWCOUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！