使用GROUP BY更新触发器 [英] Update trigger with GROUP BY

问题描述

我使用insert- / update触发器更新第二个表的列 Price 。

插入触发器似乎完美地工作，但是当我尝试在SSMS中更改单个记录时，出现错误：

lockquote

行值（s ）更新或删除，不要使行
是唯一的，或者它们改变多行（2行）。

这是我的更新触发器：

  CREATE TRIGGER [dbo]。[trgUpdateMasterData] ON [dbo]。[tabSparePartMasterData_Temp] 
 AFTER UPDATE 
 AS 
 
 UPDATE tabSparePart 
 SET Price = MD.Price 
 FROM tabSparePart INNER JOIN 
（
 SELECT插入[材料编号（SAP）] AS MaterialNumber，inserted.Price 
 FROM插入
 GROUP BY [物料编号（SAP）]，插入。价格
）MD 
 ON tabSparePart.SparePartName = MD。 MaterialNumber 
  

我需要按材料编号分组使用有多余的行插入表 tabSparePartMasterData_Temp ，我只用它来更新 tabSparePart 中的Sparepart-Price。但我认为小组会根据重复的数据进行整理（价格与任何重复相同）。

> MaterialNumber 在 tabSparepart 中不可用。在这种情况下，这个记录应该被跳过。 INNER JOIN 是否考虑到了这一点？

解决方案

尝试添加 SET NOCOUNT ON 触发器

这个错误看起来不像SQL错误，我在猜测客户端代码被触发器中的第二次更新弄糊涂了。

编辑：这个错误可能是由SSMS中的数据网格视图造成的。

这不是一个SQL消息，因为我认为：这是一个SSMS是一个愚蠢的消息

看到这些都说学习写SQL

• http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dealing-with-the-row-value-s - 更新或者（小于网志博客）
在不同的表上修改多行然后在SQL Server 2005中调用它的触发器 SSMS允许在表中重复记录，但不允许后续更新

说的是，知识库文章 a>关于SQL Server 2005中的错误...

I'm using insert-/update triggers to update a second table's column Price.

The insert trigger seems to work perfectly, but when I try to change a single record in SSMS, I get an error:

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

This is my update-trigger:

CREATE TRIGGER [dbo].[trgUpdateMasterData] ON [dbo].[tabSparePartMasterData_Temp]
AFTER UPDATE
AS

UPDATE    tabSparePart
SET      Price = MD.Price
FROM    tabSparePart INNER JOIN
(
    SELECT     inserted.[Material Number (SAP)] AS MaterialNumber, inserted.Price
    FROM          inserted
    GROUP BY [Material Number (SAP)], inserted.Price
) MD 
ON tabSparePart.SparePartName = MD.MaterialNumber

I need to group by Material-Number because there are redundant rows inserted into table tabSparePartMasterData_Temp which i'm only using to update the Sparepart-Price in tabSparePart. But i assumed that the group by would sort out the duplicates(Price is same for any duplicate).

It's possible that the inserted/updated records' MaterialNumber is not available in tabSparepart. In this case this record should be "skipped". Does the INNER JOIN takes that into account?

解决方案

Try adding SET NOCOUNT ON to the trigger

This error doesn't look like a SQL error and I'm guessing the client code is getting confused by the 2nd update in the trigger.

Edit: this error can be caused by the data grid view in SSMS being silly.

This isn't a SQL message as I thought: it is an SSMS being stupid message

See these which all says "learn to write SQL"

• http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dealing-with-the-row-value-s-updated-or (Less than dot blog)
• Trigger that modifies multiple rows on diffrent table then it was invoked on in SQL Server 2005
• SSMS permits duplicate records in a table, but not subsequent updates

Saying that, there is a KB article about a bug in SQL Server 2005...

这篇关于使用GROUP BY更新触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！