SQL Server Update Trigger (Not Unique Insert Issue)

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.

---

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)

---

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