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.

asked Feb 15 '10 at 02:34 AM in Default

Damus
65 ● 8 ● 8 ● 9

(comments are locked)

2 answers:

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.

answered Feb 15 '10 at 04:36 AM

Håkan Winther
15k ● 29 ● 35 ● 46

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

Feb 15 '10 at 09:00 AM Fatherjack ♦♦

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

Feb 15 '10 at 11:56 AM Ian Roke

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.

Feb 15 '10 at 01:06 PM TimothyAWiseman

(comments are locked)

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)