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

skass — Wed, 29 Apr 2009 04:57:00 GMT

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

Search results matching tags 'Merge' and 'Triggers'

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