title: How check :new value in compound triggers, when after statement is specified?
body: <p>I have a problem with compound trigger. :new.value is blank/empty in a <em>before each row</em> part when an <em>after</em> part is specified.</p>
  
  <p>create table part:</p>
  
  <pre><code>  DROP TABLE test_tab;
    CREATE TABLE test_tab
      ( ID_TEST_TAB NUMBER
      );
    INSERT INTO test_tab VALUES
      (1
      );
    INSERT INTO test_tab VALUES
      (2
      );
  </code></pre>
  
  <p>trigger's part:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER TEST_COMP_TRIGGER
    FOR UPDATE ON test_tab
    COMPOUND TRIGGER   
  ---
  BEFORE EACH ROW
  IS
  BEGIN
    dbms_output.put_line('TEST_COMP_TRIGGER:  BEFORE EACH ROW');
    dbms_output.put_line('&gt;&gt;&gt;&gt; old: ' || :old.ID_TEST_TAB );
    dbms_output.put_line('&gt;&gt;&gt;&gt; new: ' || :new.ID_TEST_TAB );
    NULL;
  END BEFORE EACH ROW;
  ---
  AFTER STATEMENT
  IS
  BEGIN
    NULL;
  END AFTER STATEMENT;
  END TEST_COMP_TRIGGER;
  /
  </code></pre>
  
  <p>after:</p>
  
  <pre><code>UPDATE test_tab SET test_tab.ID_TEST_TAB=test_tab.ID_TEST_TAB;
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>TEST_COMP_TRIGGER:  BEFORE EACH ROW
  &gt;&gt;&gt;&gt; old: 1
  &gt;&gt;&gt;&gt; new: 
  TEST_COMP_TRIGGER:  BEFORE EACH ROW
  &gt;&gt;&gt;&gt; old: 2
  &gt;&gt;&gt;&gt; new: 
  </code></pre>
  
  <p>If an <em>after</em> part is omitted, result is correct:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER TEST_COMP_TRIGGER
    FOR UPDATE ON test_tab
    COMPOUND TRIGGER
  ---
  BEFORE EACH ROW
  IS
  BEGIN
    dbms_output.put_line('TEST_COMP_TRIGGER:  BEFORE EACH ROW');
    dbms_output.put_line('&gt;&gt;&gt;&gt; old: ' || :old.ID_TEST_TAB );
    dbms_output.put_line('&gt;&gt;&gt;&gt; new: ' || :new.ID_TEST_TAB );
    NULL;
  END BEFORE EACH ROW;
  END TEST_COMP_TRIGGER;
  /
  </code></pre>
  
  <p>after:</p>
  
  <pre><code>UPDATE test_tab SET test_tab.ID_TEST_TAB=test_tab.ID_TEST_TAB;
  </code></pre>
  
  <p>I get:</p>
  
  <pre><code>TEST_COMP_TRIGGER:  BEFORE EACH ROW
  &gt;&gt;&gt;&gt; old: 1
  &gt;&gt;&gt;&gt; new: 2
  TEST_COMP_TRIGGER:  BEFORE EACH ROW
  &gt;&gt;&gt;&gt; old: 2
  &gt;&gt;&gt;&gt; new: 2
  </code></pre>
  
  <p>why in first case :new.value is blank?</p>
  
  <p>----------- update
  I followed yours instructions:
  code:</p>
  
  <pre><code>create or replace trigger TEST_COMP_TRIGGER
  FOR UPDATE ON test_tab  
  COMPOUND TRIGGER   
  ---- BEFORE
    BEFORE STATEMENT
    IS
    BEGIN
      dbms_output.put_line('TEST_COMP_TRIGGER:  BEFORE STATEMENT');
      NULL;
    END BEFORE STATEMENT;
  ---- AFTER
    AFTER STATEMENT IS
    BEGIN
      dbms_output.put_line('TEST_COMP_TRIGGER:  AFTER STATEMENT');
      NULL;
    END AFTER STATEMENT;
  ---- BEFORE EACH
    BEFORE EACH ROW IS
    BEGIN
      dbms_output.put_line('TEST_COMP_TRIGGER:  BEFORE EACH ROW');
      dbms_output.put_line('&gt;&gt;&gt;&gt; old: ' || :old.ID_TEST_TAB );
      dbms_output.put_line('&gt;&gt;&gt;&gt; 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;
  /
  </code></pre>
  
  <p>gives expected result:</p>
  
  <pre><code>    2 rows updated.
  TEST_COMP_TRIGGER:  BEFORE STATEMENT
  TEST_COMP_TRIGGER:  BEFORE EACH ROW
  &gt;&gt;&gt;&gt; old: 1
  &gt;&gt;&gt;&gt; new: 1
  TEST_COMP_TRIGGER:  BEFORE EACH ROW
  &gt;&gt;&gt;&gt; old: 2
  &gt;&gt;&gt;&gt; new: 2
  TEST_COMP_TRIGGER:  AFTER STATEMENT
  </code></pre>
  
  <p>but when I add AFTER EACH ROW statement:</p>
  
  <pre><code>create or replace trigger TEST_COMP_TRIGGER
  FOR UPDATE ON test_tab  
  COMPOUND TRIGGER   
  ---- BEFORE
    BEFORE STATEMENT
    IS
    BEGIN
      dbms_output.put_line('TEST_COMP_TRIGGER:  BEFORE STATEMENT');
      NULL;
    END BEFORE STATEMENT;
  ---- AFTER
    AFTER STATEMENT IS
    BEGIN
      dbms_output.put_line('TEST_COMP_TRIGGER:  AFTER STATEMENT');
      NULL;
    END AFTER STATEMENT;
  ---- BEFORE EACH
    BEFORE EACH ROW IS
    BEGIN
      dbms_output.put_line('TEST_COMP_TRIGGER:  BEFORE EACH ROW');
      dbms_output.put_line('&gt;&gt;&gt;&gt; old: ' || :old.ID_TEST_TAB );
      dbms_output.put_line('&gt;&gt;&gt;&gt; new: ' || :new.ID_TEST_TAB );
      NULL;
    END BEFORE EACH ROW;
  ---- AFTER EACH  
    AFTER EACH ROW
    IS
    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;
  /
  </code></pre>
  
  <p>I'm still getting empty :new:</p>
  
  <pre><code>2 rows updated.
  TEST_COMP_TRIGGER:  BEFORE STATEMENT
  TEST_COMP_TRIGGER:  BEFORE EACH ROW
  &gt;&gt;&gt;&gt; old: 1
  &gt;&gt;&gt;&gt; new: 
  TEST_COMP_TRIGGER:  AFTER EACH ROW
  TEST_COMP_TRIGGER:  BEFORE EACH ROW
  &gt;&gt;&gt;&gt; old: 2
  &gt;&gt;&gt;&gt; new: 
  TEST_COMP_TRIGGER:  AFTER EACH ROW
  TEST_COMP_TRIGGER:  AFTER STATEMENT    
  </code></pre>