使用 GROUP BY 更新触发器
我正在使用插入/更新触发器来更新第二个表的价格列。
插入触发器似乎工作得很好，但是当我尝试更改 SSMS 中的单个记录时，出现错误：
更新或删除的行值不会使该行 唯一的或者它们改变多行（2行）。
这是我的更新触发：
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
我需要按材料编号进行分组，因为表 tabSparePartMasterData_Temp 中插入了冗余行，我仅使用该表来更新 tabSparePart 中的备件价格代码>.但我假设分组依据会排序重复项（任何重复项的价格都相同）。
插入/更新的记录的 MaterialNumber 可能在 tabSparepart 中不可用。在这种情况下，应该“跳过”该记录。 INNER JOIN 是否考虑到了这一点？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试将
SET NOCOUNT ON添加到触发器此错误看起来不像 SQL 错误，我猜测客户端代码因触发器中的第二次更新而变得混乱。
编辑：此错误可能是由于 SSMS 中的数据网格视图愚蠢引起的。
这不是我想象的 SQL 消息：这是一条 SSMS 愚蠢的消息
请参阅这些内容，其中都写着“学习编写 SQL”
也就是说，有一篇知识库文章关于SQL Server 2005 中的错误...
Try adding
SET NOCOUNT ONto the triggerThis 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"
Saying that, there is a KB article about a bug in SQL Server 2005...