Mass Update on Table with Trigger

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paulmac106@gmail.com - 29 Jun 2007 21:20 GMT

Hi,

I need to update a field in about 20 records on a table. The table has
an update trigger (which updates the [lastedited] field whenever a
record is updated). As a result I'm getting an error: "Subquery
returned more than 1 value.", and the update fails.

Is there a way in the stored procedure to handle this issue?

thanks for your help.

Paul

Reply to this Message

Marcin A. Guzowski - 29 Jun 2007 22:04 GMT

> Hi,
>
[quoted text clipped - 4 lines]
>
> Is there a way in the stored procedure to handle this issue?

That's not an issue - it's pure ignorance. Probably someone assumed,
that only one record can be updated at a time. Trigger logic (if the
trigger is causing this error) must be changed to handle true set-based
operations.

Signature

Best regards,
Marcin Guzowski
http://guzowski.info

Reply to this Message

Erland Sommarskog - 29 Jun 2007 22:29 GMT

> I need to update a field in about 20 records on a table. The table has
> an update trigger (which updates the [lastedited] field whenever a
> record is updated). As a result I'm getting an error: "Subquery
> returned more than 1 value.", and the update fails.
>
> Is there a way in the stored procedure to handle this issue?

If you are not able to correct the trigger, you will have to run a
cursor to updaet one row at a time. But obviously that should be a very
last resort.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message