Prevent update to non-existent rows

Question

At work we have a table to hold settings which essentially contains the following columns:

• PARAMNAME
• VALUE

Most of the time new settings are added but on rare occasions, settings are removed. Unfortunately this means that any scripts which might have previously updated this value will continue to do so despite the fact that the update results in "0 rows updated" and leads to unexpected behaviour.

This situation was picked up recently by a regression test failure but only after much investigation into why the data in the system was different.

So my question is: Is there a way to generate an error condition when an update results in zero rows updated?

Here are some options I have thought of, but none of them are really all that desirable:

• PL/SQL wrapper which notices the failed update and throws an exception.
  • Not ideal as it doesn't stop anyone/a script from manually doing an update.
• A trigger on the table which throws an exception.
  • Goes against our current policy of phasing out triggers.
  • Requires updating trigger every time a setting is removed and maintaining a list of obsolete settings (if doing exclusion).
  • Might have problems with mutating table (if doing inclusion by querying what settings currently exist).

Answer 1

A PL/SQL wrapper seems like the best option to me. Triggers are a great thing to phase out, with the exception of generating sequences and inserting history records.

If you're concerned about someone manually updating rather than using the PL/SQL wrapper, just restrict the user role so that it does not have UPDATE privileges on the table but has EXECUTE privileges on the procedure.

Answer 2

Not really a solution but a method to organize things a bit:

Create a separate table with the parameter definitions and link to that table from the parameter value table. Make the reference to the parameter definition required (nulls not allowed).

Definition table PARAMS (ID, NAME)

Actual settings table PARAM_VALUES (PARAM_ID, VALUE)

(changing your table structure is also a very effective way to evoke errors in scripts that have not been updated...)