SQL Server 更新触发器（非唯一插入问题）

发布于 2024-10-17 07:17:45 字数 431 浏览 1 评论 0原文

我有一个超级简单的表，看起来像这样：

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

我在另一个表上也有一个超级简单的触发器，看起来像这样：

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

AS

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

我的问题是，当触发器运行时，我收到以下错误：

行更新或删除的值要么不会使该行唯一，要么会更改多行（2 行）。

我不明白，为什么会收到此错误？

谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

掩耳倾听 2024-10-24 07:17:45

将 SET NOCOUNT ON 添加到触发器定义的顶部。这将抑制触发器发出的影响消息的其他行并混淆 SSMS。

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)

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)

回复收藏 0 原文

我为君王 2024-10-24 07:17:45

我无法重新创建。这是否与其他触发器或约束或其他东西相冲突？我不知道。

更新：

正如 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

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

回复收藏 0 原文

~没有更多了~