Re: Getting around asensitive cursor limitation

[Paul Paulus]

Hi Matthew,

I am not that new to stored procedure programming, wrote my first SP about 20 years ago (in Oracle that is).

When possible (and to a certain extend) you should use a single SQL statement. However, if the nature of the problem on hand is such that it can't be tackled with a single SQL statement (such as your problem) you should rely on programming logic. Using a cursor and a loop to tackle your problem is quite straight forward and commonly used.

If you are using the Innodb engine (personally I never use MyIsam), you should put "for update" in the select statement of the cursor (and set autocommit to 0).

If you are updating a large number of rows you might consider using a temporary table, but not for the cursor. Use three SQL statements like this:
1) Fill the temporary table (using insert into ... select ... for update)
2) Update your table
3) Insert into your log table using the temporary table.

I would never use a trigger to tackle your problem.

BTW. To which white paper are you referring?