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.
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.
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!
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With