x

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.

more ▼

asked Feb 15, 2010 at 02:34 AM in Default

Damus gravatar image

Damus
65 8 8 9

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Feb 15, 2010 at 04:36 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

+1 - agreed, avoid triggers for this because of performance.
Feb 15, 2010 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, 2010 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, 2010 at 01:06 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
  • 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)
more ▼

answered Feb 15, 2010 at 10:59 AM

Apeman gravatar image

Apeman
311 13 13 15

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.
Feb 16, 2010 at 12:34 AM Damus
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x132
x120

asked: Feb 15, 2010 at 02:34 AM

Seen: 1013 times

Last Updated: Feb 15, 2010 at 02:34 AM