Oracle: excluding updates of one column for firing a trigger

Question

In oracle I can specify the columns, which should induce a firing of a trigger:

create or replace trigger my_trigger
before update of col1, col2, col3 on my_table for each row
begin
  // the trigger code will be executed only if col1 or col2 or col3 was updated
end;

Now I want to do the following: I don't want the trigger to fire, when only one column was updated. How is this possible?

I could list all columns except the one, which should not induce a firing of the trigger. This is quite cumbersome for tables, which many columns.

Another way would be to use the UPDATING function like this:

if not updating('COL3') then...

But if I changed COL1 and COL3, the above statement would evaluate to true.

Answer 1

I don't think there's a way you can avoid having to list all of the other columns in the table, either in the trigger body or else in the before update of ... clause.

However, you might be able to write an alter trigger on the table to regenerate the update trigger automatically if any columns are added or removed. It's a little more work, but then the maintenance should be automatic.

Answer 2

You could do something like this:

create or replace trigger my_trigger
before update on my_table
for each row
declare
   n_cols integer := 0;
begin
   for r in (select column_name from all_tab_columns
             where table_name = 'MY_TABLE'
             and owner = 'MY_SCHEMA')
   loop
      if updating(r.column_name) then
         n_cols := n_cols + 1;
         exit when n_cols > 1;
      end if;
   end loop;
   if n_cols > 1 then
      do_something;
   end if;
end;

Probably not terribly efficient though!

Answer 3

It's probably not the answer you want to hear, but are you not rather over-exaggerating the burden of maintenance? It is not normal for a table's structure t change very often once it goes into production. If you do have a table which is subject to frequent changes of column number or name, then I would suggest you have a bigger, architectural problem.

So, just type out all the column names now, and wait to see whether maintanance becomes an issue. It is certainly not worth coding a complicated implementation in a trigger - a tax which you will pay on every single update - in order to avoid an occasional changes to the DDL script.