Conditional UPDATE Triggers in Oracle
In Oracle, most triggers are defined similar to the snippet below:
create or replace trigger trigger_name
before insert or update or delete
on my_table for each row
begin
...
All demos below reference the following table:
create table demo_table(col_x varchar2(10), col_y varchar2(10));
insert into demo_table values('hello', 'world');
commit;
They're some cases where you only want the trigger to be run when updating specific columns. They're various ways to do this which are shown below.
In Trigger Definition (update of ...
)
Using the update of
clause you can explicitly list the columns that will execute the trigger when updated.
create or replace trigger demo_trigger
before update of col_x
on demo_table for each row
begin
dbms_output.put_line('***COL_X*** is being updated: ' || :new.col_x);
end;
/
set serveroutput on
-- Update another column
update demo_table
set col_y = 'world';
1 row updated.
-- Note: trigger wasn't run as it's not in the list of columns defined in "update of ..."
-- Update the "triggered" column
update demo_table
set col_x = 'goodby';
-- Note: This output shows the trigger was executed
***COL_X*** is being updated: goodby
1 row updated.
Checking Column (updating('col_name')
)
In this example, the trigger will run for all update
statements and explicit checks can be added to see if a given column is being updated.
create or replace trigger demo_trigger
before update
on demo_table for each row
begin
dbms_output.put_line('Trigger started');
if updating('col_x') then
dbms_output.put_line('***COL_X*** is being updated: ' || :new.col_x);
end if;
end;
/
-- Running the same examples as first demo
set serveroutput on
-- Update another column
update demo_table
set col_y = 'world';
Trigger started
1 row updated.
-- Note: trigger was run (as shown by "Trigger Started" message) but COL_X wasn't updated
-- Update the "triggered" column
update demo_table
set col_x = 'goodby';
Trigger started
***COL_X*** is being updated: goodby
1 row updated.
-- Trigger was run and COL_X was updated
I recommend reading the Oracle documentation on Conditional Predicates for Detecting Triggering DML Statement