THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Steve Kass

SQL MVP since 2002 Professor of Mathematics and Computer Science at Drew University

It's 2008, and @@ROWCOUNT ain't what it used to be. Don't use it in triggers.

During SQL Server 2008 beta testing, Aaron Bertrand noted that the value of @@rowcount inside a trigger could be unexpected, if the triggering statement was MERGE.

The consequences of this can be pretty bad, but fortunately there's a simple workaround. You need to do something if anyone might invoke MERGE against tables with triggers that contain @@rowcount checks

A MERGE statement can cause as many as three triggers to fire. Within each of them, the value of @@rowcount is the number of rows affected by the entire MERGE statement, i.e. the total number rows inserted, updated, or deleted by the various merge clauses.

Books Online mentions this in the article on MERGE: When used after MERGE, @@ROWCOUNT (Transact-SQL) returns the total number of rows inserted, updated, and deleted to the client.

Elsewhere, however, Books Online continues to give old advice that as of 2008 is not good advice. For example, in the article Multirow considerations for DML triggers, Books Online says, For example, the @@ROWCOUNT function can be used in the logic of the trigger to distinguish between a single and a multirow insert.

Not so, I'm afraid. The value of @@rowcount in an INSERT trigger, say, will always be at least equal to the number of rows inserted, but it can be greater. For example, a MERGE statement could have an INSERT action that doesn't occur, but an UPDATE one that updates 3 rows. The INSERT trigger will be called, because the MERGE statement's INSERT section "inserted zero rows."

Here's an example from AdventureWorks2008 to show what can go wrong.

I've added a GrandTotal table containing one row and column. GrandTotal.gt is supposed to keep track of the grand total of all purchase SubTotals.

This code creates the table and gives it a value that's initially correct for the data in AdventureWorks2008:

USE AdventureWorks2008;
GO
  CREATE
TABLE GrandTotal(
  gt DECIMAL(18,2)
);
INSERT INTO GrandTotal;
SELECT sum(SubTotal);
FROM Purchasing.PurchaseOrderHeader;
GO

This code creates a trigger to update the grand total whenever new line items are inserted into the table:

CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF
@@ROWCOUNT > 0
UPDATE GrandTotal SET
gt += (SELECT SUM(LineTotal) FROM inserted);
GO

And this MERGE statement (enclosed in a rolled-back transaction here so that no data in the database is modified by this test script) has the unintended result of updating the grand total from $63,791,994.84 to NULL.

begin tran
select
* From GrandTotal;
merge into Purchasing.PurchaseOrderDetail as P
using (
  select 2,'20010715',100,318,100,318,0,'20090101'
) as T(PurchaseOrderID,DueDate,OrderQty,ProductID,UnitPrice,ReceivedQty,RejectedQty,ModifiedDate)
on T.PurchaseOrderID = P.PurchaseOrderID
when matched then
  update set ModifiedDate = T.ModifiedDate
when not matched by target and PurchaseOrderID < 0 then
 
insert (PurchaseOrderID,DueDate,OrderQty,ProductID,UnitPrice,ReceivedQty,RejectedQty,ModifiedDate)
 
values (3,'20010715',100,318,100,318,0,'20090101');
select * From GrandTotal
rollback tran;

Oops. 

MERGE is only beginning to get used, and for a variety of triggers, a too-large @@rowcount won't cause a problem. But I think the number of people who might get caught by this problem will grow.

Don't use @@rowcount in triggers, unless you're certain it doesn't cause problems if it's higher than the real rowcount you want (which might be zero). As an alternative, Itzik suggested to me this (for INSERT; the others are similar):

declare @rc int;
with Two as (select top (2) *from inserted)
select @rc = count(*) from Two;

The value of @rc will then be 0, 1, or 2, depending on whether 0, 1, or more than 1 rows were inserted.

Steve Kass
http://www.stevekass.com

Published Wednesday, April 29, 2009 1:57 AM by skass

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Welcome to SQLblog, Steve!

April 29, 2009 8:20 AM
 

Alejandro Mesa said:

Hi Steve,

Glad to see that you are bloging here in sqlblog.

Regards the post, this is really something to keep in mind.

Cheers,

AMB

April 29, 2009 10:02 AM
 

Alexander Kuznetsov said:

Great post, Steve! Glad to see you here. I will refer this post from my series on defensive programming, if you don't mind of course.

April 29, 2009 11:17 AM
 

Alexander Kuznetsov said:

There are three kinds of triggers: those which blow up and those which fail silently ;). Seriously, there

May 11, 2009 9:19 PM
 

ww said:

Monica. Wer

December 24, 2012 5:44 PM
 

Mike DiSibio said:

2014 (year, not Sql Svr) and I'm still getting bit by this! Was very hard to find right set of google words, but thanks for this 5 yr old post that at least explains 'why' @@rowcount was '1' but nothing was in my inserted table after updates. <Bang head>

May 12, 2014 3:13 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement