question

Damus avatar image
Damus asked

Error while updating

I am using an insert trigger to store or update the values of second table while inserted in the first table by performing some calculations. For update operation also i need to update some columns in the second table. But while updating i am getting the following error. The error message is as follows:

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

Please provide a solution for this.

updatetrigger
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered

Make sure you are using the correct WHERE clause on the second table, that is, the WHERE clause has to contain the columns for the primary key/unique index.

Personally I try to avoid triggers because of the performance impact, I try to build this kind of logic in stored procedures and prevent direct access to the tables. All data access is done through stored procedures.

3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - agreed, avoid triggers for this because of performance.
2 Likes 2 ·
Ian Roke avatar image Ian Roke commented ·
+1 Stored procedures should be used but rarely possible unless you have had a hand in building the database from scratch!
1 Like 1 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Triggers do have their uses, primarily for guaranteed logging or certain types of data integrity that cannot be done with simple foreign-keys. Still, I agree they should be used only with caution and that the problem here is probably in the where clause.
0 Likes 0 ·
Apeman avatar image
Apeman answered
  • Are you sure MSSQL is giving this error message (and not your application). I asked this because I counldn't any error with this message (select * from master.sys.messages where text like '%alter multiple%').
  • Which SQL version are you using?
  • Is this error comming from inside your trigger? (maybe it's a stored procedure)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Damus avatar image Damus commented ·
Mr.Apeman, I am using sql server 2005,and i am getting this error message after updating only in the database itself and not in any application.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.