Update table row by row

[Matt]

Hello

I am trying to update a table where the trigger only allows one row at
the time to be updated, my only way around this has been to use a
cursor to perform row by row updating, this seems to have a massive
performance impact on my applications, getting rid of the trigger is
not an option as I do not control that part.

Is there another way to perform row by row updates? I have seen code
similar to this that I do not understand

while 1=1 do foooo end, is that a way around this?

Kind Regards

Matt

[Steve Jorgensen]

On 2 Apr 2004 01:57:18 -0800, ma**@fruitsalad.org (Matt) wrote:

Hello

I am trying to update a table where the trigger only allows one row at
the time to be updated, my only way around this has been to use a
cursor to perform row by row updating, this seems to have a massive
performance impact on my applications, getting rid of the trigger is
not an option as I do not control that part.

Is there another way to perform row by row updates? I have seen code
similar to this that I do not understand

while 1=1 do foooo end, is that a way around this?

First off, your DBA should be capable of writing a trigger that can handle
multi-row updates. Otherwise, I don't think there's any option faster than
using a cursor.

It's likely that the same cursor that's limiting you to updating one row at a
time is actually the biggest bottleneck in the process, so improving the
performance of your loop would make no difference whatsoever. Basically, the
trigger is likely running one or more queries joining the inserted row to one
or more tables, and by repeatedly re-running those same queries each time you
do an insert, it's wasing vast amounts of time. If, instead, the trigger
could perform the same query joining the entire INSERTED virtual table for a
bulk insert at once, things could be very much faster.