oracle - How check :new value in compound triggers, when after statement is specified? -

i have problem compound trigger. :new.value blank/empty in before each row part when after part specified.

create table part:

  drop table test_tab;   create table test_tab     ( id_test_tab number     );   insert test_tab values     (1     );   insert test_tab values     (2     ); 

trigger's part:

create or replace trigger test_comp_trigger   update on test_tab   compound trigger    --- before each row begin   dbms_output.put_line('test_comp_trigger:  before each row');   dbms_output.put_line('>>>> old: ' || :old.id_test_tab );   dbms_output.put_line('>>>> new: ' || :new.id_test_tab );   null; end before each row; --- after statement begin   null; end after statement; end test_comp_trigger; / 

after:

update test_tab set test_tab.id_test_tab=test_tab.id_test_tab; 

i get:

test_comp_trigger:  before each row >>>> old: 1 >>>> new:  test_comp_trigger:  before each row >>>> old: 2 >>>> new:  

if after part omitted, result correct:

create or replace trigger test_comp_trigger   update on test_tab   compound trigger --- before each row begin   dbms_output.put_line('test_comp_trigger:  before each row');   dbms_output.put_line('>>>> old: ' || :old.id_test_tab );   dbms_output.put_line('>>>> new: ' || :new.id_test_tab );   null; end before each row; end test_comp_trigger; / 

after:

update test_tab set test_tab.id_test_tab=test_tab.id_test_tab; 

i get:

test_comp_trigger:  before each row >>>> old: 1 >>>> new: 2 test_comp_trigger:  before each row >>>> old: 2 >>>> new: 2 

why in first case :new.value blank?

----------- update followed yours instructions: code:

create or replace trigger test_comp_trigger update on test_tab   compound trigger    ---- before   before statement     begin     dbms_output.put_line('test_comp_trigger:  before statement');     null;   end before statement; ---- after   after statement   begin     dbms_output.put_line('test_comp_trigger:  after statement');     null;   end after statement; ---- before each   before each row   begin     dbms_output.put_line('test_comp_trigger:  before each row');     dbms_output.put_line('>>>> old: ' || :old.id_test_tab );     dbms_output.put_line('>>>> new: ' || :new.id_test_tab );     null;   end before each row; end test_comp_trigger; /  ----- set serveroutput on format wraped; update test_tab set test_tab.id_test_tab=test_tab.id_test_tab; / 

gives expected result:

    2 rows updated. test_comp_trigger:  before statement test_comp_trigger:  before each row >>>> old: 1 >>>> new: 1 test_comp_trigger:  before each row >>>> old: 2 >>>> new: 2 test_comp_trigger:  after statement 

but when add after each row statement:

create or replace trigger test_comp_trigger update on test_tab   compound trigger    ---- before   before statement     begin     dbms_output.put_line('test_comp_trigger:  before statement');     null;   end before statement; ---- after   after statement   begin     dbms_output.put_line('test_comp_trigger:  after statement');     null;   end after statement; ---- before each   before each row   begin     dbms_output.put_line('test_comp_trigger:  before each row');     dbms_output.put_line('>>>> old: ' || :old.id_test_tab );     dbms_output.put_line('>>>> new: ' || :new.id_test_tab );     null;   end before each row; ---- after each     after each row     begin     dbms_output.put_line('test_comp_trigger:  after each row');   end after each row; end test_comp_trigger; /  ----- set serveroutput on format wraped; update test_tab set test_tab.id_test_tab=test_tab.id_test_tab; / 

i'm still getting empty :new:

2 rows updated. test_comp_trigger:  before statement test_comp_trigger:  before each row >>>> old: 1 >>>> new:  test_comp_trigger:  after each row test_comp_trigger:  before each row >>>> old: 2 >>>> new:  test_comp_trigger:  after each row test_comp_trigger:  after statement     

the order of timing-point sections matters , needs be:

before statement after statement before each row after each row 

ref: compound trigger sections, oracle 11gr1 docs

the following script (run in oracle sql developer connected oracle database 11g express edition release 11.2.0.2.0 - production

drop table test_tab; create table test_tab ( id_test_tab number); insert test_tab values (1); insert test_tab values (2);  create or replace trigger test_comp_trigger update on test_tab   compound trigger       after statement   begin     dbms_output.put_line('test_comp_trigger:  after statement');     null;   end after statement;    before each row   begin     dbms_output.put_line('test_comp_trigger:  before each row');     dbms_output.put_line('>>>> old: ' || :old.id_test_tab );     dbms_output.put_line('>>>> new: ' || :new.id_test_tab );     null;   end before each row;  end test_comp_trigger; /  set serveroutput on format wraped; update test_tab set test_tab.id_test_tab=test_tab.id_test_tab+1; / 

produces output:

table test_tab dropped. table test_tab created. 1 rows inserted. 1 rows inserted. trigger test_comp_trigger compiled 2 rows updated. test_comp_trigger:  before each row >>>> old: 1 >>>> new: 2 test_comp_trigger:  before each row >>>> old: 2 >>>> new: 3 test_comp_trigger:  after statement 

swapping them wrong order output ends as:

table test_tab dropped. table test_tab created. 1 rows inserted. 1 rows inserted. trigger test_comp_trigger compiled 2 rows updated. test_comp_trigger:  before each row >>>> old: 1 >>>> new:  test_comp_trigger:  before each row >>>> old: 2 >>>> new:  test_comp_trigger:  after statement