ORACLE "before update" trigger does not work when a column is changed in another trigger

Question

I am using ORACLE 12c.

In the table, I have two triggers, both “before update”. One of the triggers when a column is updated, and inside this trigger, the other column receives a new value. The second trigger should fire when the second column is updated. 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 inserted the row, I do UPDATE. And I expect that COL1 = "now we will see", COL2 = "only testing" and COL3 = "trigger_test_2" fired ".

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

But I get the following:

select * from TRIGGER_TEST;


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

Can someone explain this to me? I am really sure that with previous versions of ORACLE this szenario worked. But now this is not so.

Answer 1

, ORACLE szenario .

. 11gR2 :

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                        

before update of COL2 on TRIGGER_TEST - DML. DML:

DML , , DML DELETE, INSERT UPDATE....

DML . :

    :new.col2 := 'only testing';

.. DML - .

, :

    :new.col1 := 'something';

... , ORA-00036: maximum number of recursive SQL levels (50) exceeded. , .

col3 , , . , , , , ( ), . , , , DML , .

Answer 2

" COL2" SQL-, UPDATE MERGE, . ?