1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • What generates the error message please? It doesn't look like SQL... – gbn Aug 15 '11 at 16:01
  • Is there by chance a unique constraint on `(tabSparePart.Price)` or on `(tabSparePart.SparePartName, tabSparePart.Price)`? If there are two or more identical `tabSparePart.SparePartName`, your update can violate the constraint, and maybe that's what actually happened. – Andriy M Aug 15 '11 at 16:11
  • @gbn: SQL-Server Management-Studio (SSMS) when i try to change a price value in the table with the trigger. – Tim Schmelter Aug 15 '11 at 16:12
  • Please add table definitions please and the update code – gbn Aug 15 '11 at 16:13
  • @Andriy: Yes, the is a unique contstraint on `tabSparePart.SparePartName`. But i don't want to insert records there but only update the related price. – Tim Schmelter Aug 15 '11 at 16:14
  • 1
    @Tim: If `SparePartName` is unique and `Price` not necessarily then it's not what I thought. And answering your question about `MaterialNumber` not available in `tabSparepart`, yes, INNER JOIN takes that into account by yielding an empty row set, so no update can take place then. – Andriy M Aug 15 '11 at 16:26

1 Answers1

5

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"

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

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I've changed the trigger to `SET NOCOUNT ON` but the error is the same. What do you mean with "2nd update"? I'm updating a different table than the table which belongs to this trigger. – Tim Schmelter Aug 15 '11 at 16:10
  • @Tim Schmelter: there are *two* updates here even if on different tables. Some clients get confused by this. http://stackoverflow.com/questions/1483732/set-nocount-on-usage – gbn Aug 15 '11 at 16:14
  • You are right. I added the `NOCOUNT` at the top of the trigger but i had to insert it straight above the `UPDATE`. Thank you. – Tim Schmelter Aug 15 '11 at 16:22