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 with 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 at once, the statement evaluates to false. That's not what I want since, I want to restrict the execution when only one column (COL3) was updated.

Solution 1

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!

Solution 2

I had the same problem yesterday. I wanted to code a trigger that fired on every field except one, the table had 103 colums.

First I coded:

if (:OLD.col1<>:NEW.col1 or :OLD.col2<>:NEW.col2 or :OLD.col3<>:NEW.col3 ....)

But I had some problems with null values, so I added:

if (NVL(:OLD.col1,0)<>NVL(:NEW.col1,0) or NVL(:OLD.col2,0)<>NVL(:NEW.col2,0)  ....)

But then I had some problems with DATE columns, it became a mess..

I think that the best solution is to list all columns that you want to verify in the "OF":

AFTER INSERT OR UPDATE of cOL1, col2, col3 ... colN ON table1

It was not "elegant" but... it worked perfect.

Solution 3

It's probably not the answer you want to hear, but I think you are rather over-exaggerating the burden of maintenance. It is not normal for a table's structure to change very often after 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.