Getting around asensitive cursor limitation

[Matthew Smith]

Hi Folks

I'm rather new to stored procedures (been doing them a week now), so please bear with me!

In its simplest form (and this is what I am doing at the moment), I want to do this:

update my_table set [whatever] where [condition].

I would like, however, to log each update individually to another table. If, when a cursor is declared, the cursor were a COPY of selected data, I could select all the record IDs into a cursor, then run through the cursor updating each row individually and inserting records into the log table.

However, the stored procedures white paper advises against making updates this way due to this 'asensitivity'.

I don't think that I can use triggers to do the logging for me because a) I don't want this type of logging to occur for all updates to the table and b) not all the data that goes into the log table would be available to the trigger (things passed as parameters to the stored procedure.)

I was wondering if anyone had any hacks or recipes to get around the limitations of cursors in stored procedures.

All that I can think of is to select the IDs of the rows to update into a temporary table and then select that into a cursor and do the above. This sounds a bit long-winded to me - is there a simpler, more efficient way to do this?

Cheers

M