Get ROWCOUNT from INSTEAD OF TRIGGER

Question

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?

I mean decide whether or not to actually conduct the INSERT. Depending on the values of the INSERTED TABLE, no INSERT might occur. I'll update my question so its clearer. — Laramie, Nov 26 '10 at 23:29
When you've got one INSTEAD OF trigger whose action you're trying to override by using another INSTEAD OF trigger my gut reaction is you've got too much logic in triggers, and you may have been drawn in by the Dark Side of the Database. That way lies madness. Turn back, turn back, before it's too late! Aiiieeee! Cthulhu fthagn!!! (Don't ask me how I know...) — Bob Jarvis - Слава Україні, Nov 27 '10 at 00:55
@Bob. I love the "abandon all hope" sentiment and hear you loud and clear. What I'm looking at in this case is a single trigger that may or may not do the insert depending on "stuff". Nefarious still, but why can't I get an accurate row count? — Laramie, Nov 27 '10 at 01:03
sounds like impedance mismatch. In other words, your expectations and desires regarding the database server doesn't quite match what the server actually does. As you've noted, @@ROWCOUNT is counting the number of attempts (I'm guessing this is the number of INSERTs your code actually issues) rather than the number of successes which occur during trigger execution. If possible, you might be better off refactoring a bit to remove the trigger. Triggers are not evil, but they can bite you when and where you least expect it. In my experience, "That trigger is best which does least". — Bob Jarvis - Слава Україні, Nov 27 '10 at 01:14

Lex · Accepted Answer · 2010-11-27T00:05:16.783

3

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

edited Nov 27 '10 at 00:05

answered Nov 26 '10 at 23:55

Lex

1,378
11
10

The truth is the only statement in the trigger that should affect @@ROWCOUNT is the insert itself. I updated the example based on your observation. The SELECT in the EXISTS test doesn't actually occur in the actual statement. As I noted, I've "tried with NOCOUNT ON and OFF". It doesn't seem to have an effect. @@ROWCOUNT always reports the number of rows that attempted an insert, not the number that were actually inserted. – Laramie Nov 27 '10 at 00:32
It turns out that the DONE_IN_PROC value returned to the client is the sum of all records affected. The best solution was to SET NOCOUNT ON in a procedure that did the updates, then SET NOCOUNT OFF in the trigger itself. This returns the actual number of records inserted by the trigger. Your answer was close to what I was looking for. Thanks:) – Laramie Nov 27 '10 at 05:51
@Laramie and Lex: I realize this was asked 4 years ago, but if you are still interested in the answer, there is in fact a more direct way of passing this (and other) information along. Please see the answer I just posted :). – Solomon Rutzky Nov 22 '14 at 20:22

score 2 · Answer 2 · edited Apr 13 '17 at 12:42

The Problem

I need information in the context of the main process that is only available in the context of the trigger.

The Solution

Whether getting @@ROWCOUNT or anything else from a trigger, or even passing information to a trigger, there are two methods that allow for sharing information with triggers:

SET CONTEXT_INFO / CONTEXT_INFO()
Local Temporary Tables (i.e. tables with names starting with a single #: #tmp)

I posted an example of using CONTEXT_INFO in an answer on a related question over at DBA.StackExchange: Passing info on who deleted record onto a Delete trigger. There was a discussion in the comments on that answer related to possible complications surrounding CONTEXT_INFO, so I posted another answer on that question using a temporary table instead.

Since that example dealt with sending information to a trigger, below is an example of getting information from a trigger as that is what this question is about:

First: Create a simple table

CREATE TABLE dbo.InsteadOfTriggerTest (Col1 INT);

Second: Create the trigger

CREATE TRIGGER dbo.tr_InsteadOfTriggerTest
   ON dbo.InsteadOfTriggerTest
   INSTEAD OF INSERT
AS
BEGIN
   PRINT 'Trigger (starting): ' + CONVERT(NVARCHAR(50), @@ROWCOUNT);

   SET NOCOUNT ON; -- do AFTER the PRINT else @@ROWCOUNT will be 0

   DECLARE @Rows INT;
   INSERT INTO dbo.InsteadOfTriggerTest (Col1)
      SELECT TOP (5) ins.Col1
      FROM inserted ins;

   SET @Rows = @@ROWCOUNT;
   PRINT 'Trigger (after INSERT): ' + CONVERT(NVARCHAR(50), @Rows);

   -- make sure temp table exists; no error if table is missing
   IF (OBJECT_ID('tempdb..#TriggerRows') IS NOT NULL)
   BEGIN
      INSERT INTO #TriggerRows (RowsAffected)
      VALUES (@Rows);
   END;
END;

Third: Do the test

SET NOCOUNT ON;

IF (OBJECT_ID('tempdb..#TriggerRows') IS NOT NULL)
BEGIN
    DROP TABLE #TriggerRows;
END;
CREATE TABLE #TriggerRows (RowsAffected INT);

INSERT INTO dbo.InsteadOfTriggerTest (Col1)
   SELECT so.[object_id]
   FROM   [master].[sys].[objects] so;

PRINT 'Final @@ROWCOUNT (what we do NOT want): ' + CONVERT(NVARCHAR(50), @@ROWCOUNT);

SELECT * FROM #TriggerRows;

Output (in Messages tab):

Trigger (starting): 91
Trigger (after INSERT): 5
Final @@ROWCOUNT (what we do NOT want): 91

Results:

RowsAffected
5

Get ROWCOUNT from INSTEAD OF TRIGGER

2 Answers2

The Problem

The Solution

Linked