SQL Server更新触发器(不是唯一的插入问题)

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).

更新或删除的行值不是使行的唯一，就是它们改变了多行(2行)。

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

我不明白，为什么会有这个错误?

Thank you.

谢谢你！

2 个解决方案

#1

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.

在触发器定义的顶部添加SET NOCOUNT。这将抑制来自触发器的受影响的附加行，并混淆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)

#2

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:

正如Mikael所说，在TableXYZ中添加主键将解决这个问题。只有在使用SSMS修改表时才会发生。谢谢米凯尔。如此:

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

#1