SQL Server Home
Katmai : Merge does not distinguish rowcounts in triggers
as Won't Fix
9/17/2007 7:58:18 AM
User(s) can reproduce this bug
Many people rely on checking @@ROWCOUNT in triggers to determine whether to perform certain operations. When a MERGE statement fires, it passes the sum of rows affected in any INSERT, UPDATE or DELETE operations, instead of the individiual rowcounts for each operation. This occurs in both a combined trigger (for INSERT, UPDATE, DELETE) and individual triggers that capture each type of operation.
I realize that people should move away from @@ROWCOUNT for this type of control of flow, however there is a lot of code out there right now that is going to break, and unless this behavior is properly documented, it will be without warning.
SQL Server 2008 July CTP
Operating System Language
Steps to Reproduce
CREATE DATABASE merge_repro;
USE merge_repro; SET NOCOUNT ON;
CREATE TABLE dbo.Employees
EmployeeID INT PRIMARY KEY,
SELECT 1, 'Bob', 12000
UNION ALL SELECT 2, 'Frank', 14000
UNION ALL SELECT 3, 'Kirsten', 13000
CREATE TABLE dbo.Employees_Change
SELECT 1, 'Bob', 13000
UNION ALL SELECT 3, 'Kirsten', 13250
UNION ALL SELECT 4, 'Joe', 14000
CREATE TRIGGER dbo.trEmpInsert
PRINT 'Insert ROWCOUNT = ' + RTRIM(@@ROWCOUNT);
CREATE TRIGGER dbo.trEmpUpdate
PRINT 'Update ROWCOUNT = ' + RTRIM(@@ROWCOUNT);
CREATE TRIGGER dbo.trEmpDelete
PRINT 'Delete ROWCOUNT = ' + RTRIM(@@ROWCOUNT);
dbo.Employees AS [existing] -- target
dbo.Employees_Change AS [new] -- source
([existing].EmployeeID = [new].EmployeeID)
MATCHED AND ([existing].Salary <> [new].Salary)
UPDATE SET [existing].Salary = [new].Salary
INSERT (EmployeeID, [Name], Salary)
VALUES ([new].EmployeeID, [new].[Name], [new].Salary)
SOURCE NOT MATCHED
DROP DATABASE merge_repro;
Insert ROWCOUNT = 4
Update ROWCOUNT = 4
Delete ROWCOUNT = 4
Insert ROWCOUNT = 1
Update ROWCOUNT = 2
Delete ROWCOUNT = 1
to post a comment.
Please enter a comment.
on 2/7/2013 at 6:46 AM
Closed as won't fix with no explanation. <sigh>
on 2/12/2012 at 7:45 AM
This will be very helpful.
on 8/28/2009 at 6:31 AM
"For a future release of SQL Server, we'll look into providing a mechanism to easily retrieve the row count specific to each DML action in a MERGE statement."
That gets my vote!!!
on 1/31/2008 at 6:41 PM
Thanks for the feedback. By design, when used with MERGE, @@ROWCOUNT returns the total number of rows inserted, updated, and deleted. This is currently documented in the "MERGE (Transact-SQL)" topic in SQL Server 2008 Books Online [http://msdn2.microsoft.com/en-us/library/bb510625(SQL.100).aspx]. In order to clarify this further, we are working on calling out this specific behavior, i.e. @@rowcount in triggers, in the documentation.
For a future release of SQL Server, we'll look into providing a mechanism to easily retrieve the row count specific to each DML action in a MERGE statement.
-- SQL Server Engine Team
to post a workaround.
Please enter a workaround.
© 2014 Microsoft