ORACLE "before update" trigger does not fire when column is changed within another trigger

Question

I am using ORACLE 12c.

On a table I have 2 triggers, both "before update". One of the triggers fires while updating a column and within this trigger another column gets a new value. The second trigger should fire while updating this second column. But he did not.

create table TRIGGER_TEST
(
    col1 varchar2(64),
    col2 varchar2(64),
    col3 varchar2(64)
);

create or replace trigger TR_TRIGGER_TEST_1 
before update of COL1 on TRIGGER_TEST
for each row
begin
    dbms_output.put_line('here we are in TR_TRIGGER_TEST_1');
    :new.col2 := 'only testing';
end;
/

create or replace trigger TR_TRIGGER_TEST_2
before update of COL2 on TRIGGER_TEST
for each row
begin
    dbms_output.put_line('here we are in TR_TRIGGER_TEST_2');
    :new.col3 := 'trigger_test_2 has fired';
end;
/


insert into TRIGGER_TEST values ('1_col1','1_col2','1_col3');
select * from TRIGGER_TEST;

COL1                 COL2              COL3                                                    
----------------------------------------------------------------
1_col1               1_col2            1_col3

After I have inserted the row I perform an UPDATE. And I expect COL1= "now we will see", COL2="only testing" and COL3 = "trigger_test_2 has fired".

update TRIGGER_TEST set COL1 = 'now we will see';

But what I get is this:

select * from TRIGGER_TEST;


COL1                 COL2              COL3                                                    
----------------------------------------------------------------
now we will see      only testing      1_col3

Can anybody explain this to me? I am really sure, that with former ORACLE versions this szenario has worked. But now it does not.

Welcome to SO! This is your first question and it is well written, with a meaningful title, right tags, good formatting; it even shows what you did and allows people to reproduce the test. +1 ! — Aleksej, Jun 07 '17 at 12:51
.. and if you want to keep using SO the right way, [here](http://stackoverflow.com/help/someone-answers) you find something on what to do when someone answers you. — Aleksej, Jun 07 '17 at 13:43

score 4 · Accepted Answer · answered Jun 07 '17 at 13:15

I am really sure, that with former ORACLE versions this szenario has worked.

It has not. I ran your code in 11gR2 and got the same result:

set serveroutput on

update TRIGGER_TEST set COL1 = 'now we will see';

here we are in TR_TRIGGER_TEST_1


1 row updated.

select * from TRIGGER_TEST;

COL1                           COL2                           COL3                          
------------------------------ ------------------------------ ------------------------------
now we will see                only testing                   1_col3

The before update of COL2 on TRIGGER_TEST is a DML event clause. You are creating simple DML triggers:

A DML trigger is created on either a table or view, and its triggering event is composed of the DML statements DELETE, INSERT, and UPDATE. ...

When you issue your update that DML causes the first trigger to fire. But when you assign a new value inside that trigger:

    :new.col2 := 'only testing';

.. that is not a DML statement - it is not a separate update.

If assigning a value in that way did cause a trigger to fire, then if you instead did:

    :new.col1 := 'something';

... then that first trigger would fire again, recursively, until you hit the error ORA-00036: maximum number of recursive SQL levels (50) exceeded. That would obviously be bad.

You will have to repeat the assignment of col3 in the first trigger if that is what you need to happen. For more complicated side-effects that you want to happen whether you hit either trigger, you could have a procedure that does any necessary actions (that don't affect this table), and then call that from both triggers. Although then you'd need a mechanism to make sure the procedure isn't called twice if a DML update touches both columns - which would cause both triggers to fire.

Thank you very much for your explanation! Now I understand why it does not work..... — virtualbee, Jun 07 '17 at 13:43

score 0 · Answer 2 · answered Jun 07 '17 at 13:15

0

"update of COL2" in a trigger would mean update using a SQL statement such as UPDATE or MERGE, and not otherwise. Why dont you code the second trigger in the first ?

answered Jun 07 '17 at 13:15

Allen

48
1
4

ORACLE "before update" trigger does not fire when column is changed within another trigger

2 Answers2