Error while updating

Question

question

Damus asked Feb 15, '10

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.

update trigger

10 |1200

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

Answer 1 · 2010-02-15T04:36:35Z

Håkan Winther answered Feb 15, '10

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

10 |1200

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

Fatherjack ♦♦ commented · Feb 15, 2010 at 09:00 AM

+1 - agreed, avoid triggers for this because of performance.

2 ·

Ian Roke commented · Feb 15, 2010 at 11:56 AM

+1 Stored procedures should be used but rarely possible unless you have had a hand in building the database from scratch!

1 ·

TimothyAWiseman commented · Feb 15, 2010 at 01:06 PM

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 ·

Answer 2 · 2010-02-15T10:59:06Z

Apeman answered Feb 15, '10

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

10 |1200

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

Damus commented · Feb 16, 2010 at 12:34 AM

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 ·

question