9

I have a super simple table that looks something like this:

CREATE TABLE [dbo].[TestTable](
[SomeColumn] [int] NOT NULL )

I also have a super simple trigger on another table that looks something like this:

ALTER TRIGGER [dbo].[trg_Audit_TableXYZ] ON [dbo].[TableXYZ] AFTER UPDATE

AS

INSERT INTO [dbo].[TestTable] Values (123)

My problem is that when the trigger runs I get the following error:

The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (2 rows).

I don't get it, why would I get this error?

Thank you.

Dot Net Luchador
  • 103
  • 1
  • 5
  • does the trigger really contain the value '123' ? – Mitch Wheat Feb 13 '11 at 04:22
  • Yes, I just hardcoded the value to 123 to keep the trigger as simple as it could possibly be. I make sure that the "TestTable" has zero rows when I run the trigger and I only update a single cell on table that has the trigger. Even if I remove the primary key constrain I still get the same error (just tried that, I will update the post). – Dot Net Luchador Feb 13 '11 at 05:36
  • 2
    Do you use MSSMS to edit the rows in `TableXYZ`? Similar problem here http://stackoverflow.com/questions/901931/trigger-that-modifies-multiple-rows-on-diffrent-table-then-it-was-invoked-on-in-s. Turns out to be problems with duplicate rows when editing with MSSMS but works just fine when updating is done in code. You need a primary key in `TableXYZ`. – Mikael Eriksson Feb 13 '11 at 06:06

2 Answers2

21

Add SET NOCOUNT ON to the top of the trigger definition. This will suppress the additional rows affected message that emanates from the trigger and confuses SSMS.

i.e.

ALTER TRIGGER [dbo].[trg_Audit_TableXYZ] 
ON [dbo].[TableXYZ] 
AFTER UPDATE
AS
SET NOCOUNT ON
--Rest of trigger definition follows
INSERT INTO [dbo].[TestTable] Values (123)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

I can't recreate. Is this conflicting with some other trigger or a constraint or something, maybe? I don't know.

Update:

As Mikael said, adding a primary key in TableXYZ will work-around the issue. Only happens when you are modifying the table with SSMS. Thanks Mikael. This works:

create database testdb
go
use testdb

CREATE TABLE [dbo].[TestTable](
[SomeColumn] [int] NOT NULL)

CREATE TABLE [dbo].[TableXYZ](
[ID] [int] identity(1,1) primary key,
[SomeColumn] [int] NOT NULL )
go

create TRIGGER [dbo].[trg_Audit_TableXYZ] ON [dbo].[TableXYZ] AFTER UPDATE
AS
INSERT INTO [dbo].[TestTable] Values (123)
go

INSERT INTO [dbo].[Tablexyz] Values (4)
INSERT INTO [dbo].[Tablexyz] Values (5)
INSERT INTO [dbo].[Tablexyz] Values (6)

update tablexyz set somecolumn = 789
update tablexyz set somecolumn = 0
matt.dolfin
  • 672
  • 4
  • 9
  • Try to edit with SSMS instead of calling update in code. Right-click table `Tablexyz` - select `Edit Top 200 Rows` - change value of one row. – Mikael Eriksson Feb 13 '11 at 06:40
  • Oh, I see. That's unfortunate. That can't be expected behavior. I will update my post. – matt.dolfin Feb 13 '11 at 07:23
  • I was actually getting the same error on SSMS as well as code with one exception. The example on my original post failed on SSMS but works on code, but if I used a sub query on the insert like "INSERT INTO [dbo].[TestTable] Select 123 Union All Select 456" then it failed on both SSMS and code. Adding the "SET NOCOUNT ON" statement fixed all the problems. Thanks. – Dot Net Luchador Feb 13 '11 at 18:20