CODEWITHSUNDEEP
sqlsql-serversql-server-2008triggers
Laramie
Laramie

Reputation: 5587

Get ROWCOUNT from INSTEAD OF TRIGGER

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?

Upvotes: 6

Views: 6298

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48864

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:

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

Upvotes: 2

Lex
Lex

Reputation: 1378

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

Upvotes: 3

Related Questions