DML Issued by DDL Trigger is not Replicated with Streams (Doc ID 880658.1)

Last updated on MARCH 24, 2012

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 24-Mar-2012***

Symptoms

-- Problem Statement:
Under a special scenario, some updates from DML triggers are not being captured and applied in streams environments. When having replicated table with streams (DDL and DML), if we have DML trigger on this table, and this trigger has been fired by any DDL statement (like alter table add column for example) , the DML changes by the DML trigger will not be able to replicate to the target table, please see the following example :

Test Case

#--------------------------------------------------------------------
# STEP 1:
#   where  : in the source database
#--------------------------------------------------------------------

truncate table scott.emp;
alter table scott.emp add(stamp date);

create or replace trigger scott.emp_trigger before insert or update on scott.emp for each row
declare
  pragma autonomous_transaction;
begin
   if inserting
   then
      :new.hiredate := sysdate;
   elsif updating
   then
      :new.stamp := sysdate;
   end if;            
   commit;
end;
/

insert into scott.emp(empno,ename,hiredate) values (100,null,null);
insert into scott.emp(empno,ename,hiredate) values (200,null,null);
insert into scott.emp(empno,ename,hiredate) values (300,null,null);
insert into scott.emp(empno,ename,hiredate) values (400,null,null);

commit;

SQL> select empno,ename,hiredate,stamp from scott.emp;

     EMPNO ENAME      HIREDATE  STAMP
---------- ---------- --------- ---------
       100            31-JUL-09
       200            31-JUL-09
       300            31-JUL-09
       400            31-JUL-09

#--------------------------------------------------------------------
# STEP 2: check data is available
#   where:  on the target database
#--------------------------------------------------------------------
SQL> select empno,ename,hiredate,stamp from scott.emp;

     EMPNO ENAME      HIREDATE  STAMP
---------- ---------- --------- ---------
       100            31-JUL-09
       200            31-JUL-09
       300            31-JUL-09
       400            31-JUL-09

#--------------------------------------------------------------------
# STEP 3: change the table structure  
#   where:  on the source database
#--------------------------------------------------------------------

SQL> alter table scott.emp add (some_more_data  varchar2(10) default 'data');

Table altered.

SQL> select empno,ename,hiredate,stamp,some_more_data from scott.emp;

     EMPNO ENAME      HIREDATE  STAMP     SOME_MORE_
---------- ---------- --------- --------- ----------
       100            31-JUL-09 31-JUL-09 data
       200            31-JUL-09 31-JUL-09 data
       300            31-JUL-09 31-JUL-09 data
       400            31-JUL-09 31-JUL-09 data
       
       
       ==> this is the expeted data:  - ins_date was filled during insert
                                      - some_more_data was filled by the 'alter table' statement
                                      - mod_date was filled by the trigger because the database has update each row, as a result of the "defautl value" instructed by the alter table  
                                      
                                      
#--------------------------------------------------------------------
# STEP 4: check the structure + data in the target
#   where:  on the source database
#--------------------------------------------------------------------
                               SQL>  select empno,ename,hiredate,stamp,some_more_data from scott.emp;

     EMPNO ENAME      HIREDATE  STAMP     SOME_MORE_
---------- ---------- --------- --------- ----------
       100            31-JUL-09           data
       200            31-JUL-09           data
       300            31-JUL-09           data
       400            31-JUL-09           data





Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms