Are your triggers triggered by Streams?

That sounds like a fair question, don't you think? Like often with Oracle the answer is split between "It depends" and "That's not as easy as I thought first". Let's start with how it's supposed to work; From the documentation, How an apply process behaves depends on the fire_once property of the trigger:

• If the trigger fire_once is TRUE, then the trigger is not executed by an apply process
• If the trigger fire_once is FALSE, then the trigger is suppose to be always executed, wether that's a regular DML/DDL command or an Streams apply process performing the change

That sounds easy. Lets have a look with a concrete example!

To build the test case, we'll implement a simple Streams One-Way Replication. To do it in 10 minutes, assuming your database is in archivelog, follow Step 1 to Step 9 of the "Oracle Streams One Way Table Replication 101" post. Ready?

Test when fire_once is TRUE (i.e. Default)

To begin, we'll create a table and a trigger in the DESTINATION schema; we'll check if the trigger is supposed to fire ONCE or ALWAYS:

connect / as sysdba

create table destination.t1_x
  (id   number primary key,
   text varchar2(80));

create or replace trigger destination.t1_x_trigger
 after insert on destination.T1 for each row 
  begin
    insert into T1_X(id, text)
      values (:new.id, :new.text);
  end;
/

set serveroutput on
begin
 if (dbms_ddl.IS_TRIGGER_FIRE_ONCE('DESTINATION', 'T1_X_TRIGGER'))
   then
    dbms_output.put_line('Trigger FIRE_ONCE=TRUE');
  else
    dbms_output.put_line('Trigger FIRE_ONCE=FALSE');
  end if;
end;
/

Trigger FIRE_ONCE=TRUE

So that's really the default and that trigger is not supposed to be executed by an apply process. But before, we can test the trigger is working fine by inserting a value in the DESTINATION.T1 table manually (That will make the 2 tables diverge but who cares?):

insert into DESTINATION.T1
   values (9999, 'Text 9999');
commit;

select * 
  from DESTINATION.T1_X;

  ID TEXT
---- ---------
9999 Text 9999

Now that we are sure the tigger is working fine, we can test the trigger is not executed by the apply process. We'll change the SOURCE.T1table this time and will check the values in changed on the DESTINATION.T1 but not in DESTINATION.T1_X as expected:

insert into SOURCE.T1
   values (4, 'Text 4');
commit;

pause

select * 
  from DESTINATION.T1;

  ID TEXT
---- ---------
   1 Text 1
   2 Text 2
   3 Text 3
9999 Text 9999
   4 Text 4

select * 
  from DESTINATION.T1_X;

  ID TEXT
---- ---------
9999 Text 9999

Very good!

Test when fire_once is FALSE

Now we'll change the trigger's behavior and we'll perform another test to see if the trigger is executed when the change is applied to the DESTINATION.T1 table:

begin
  DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY(
   trig_owner => 'DESTINATION', 
   trig_name  => 'T1_X_TRIGGER',
   fire_once  => false);
end;
/

set serveroutput on
begin
 if (dbms_ddl.IS_TRIGGER_FIRE_ONCE('DESTINATION', 'T1_X_TRIGGER'))
   then
    dbms_output.put_line('Trigger FIRE_ONCE=TRUE');
  else
    dbms_output.put_line('Trigger FIRE_ONCE=FALSE');
  end if;
end;
/

Trigger FIRE_ONCE=FALSE

insert into SOURCE.T1
   values (5, 'Text 5');
commit;

pause

select * 
  from DESTINATION.T1;

  ID TEXT
---- ---------
   1 Text 1
   2 Text 2
   3 Text 3
9999 Text 9999
   4 Text 4
   5 Text 5

select * 
  from DESTINATION.T1_X;

  ID TEXT
---- ---------
9999 Text 9999

But wait a minute! Wasn't that trigger supposed to be executed? Let's restart all the processes to see if anything changes:

exec dbms_capture_adm.stop_capture('STREAMS_CAPTURE');
exec dbms_apply_adm.stop_apply('STREAMS_APPLY');
exec dbms_capture_adm.start_capture('STREAMS_CAPTURE');
exec dbms_apply_adm.start_apply('STREAMS_APPLY');

set serveroutput on
begin
 if (dbms_ddl.IS_TRIGGER_FIRE_ONCE('DESTINATION', 'T1_X_TRIGGER'))
   then
    dbms_output.put_line('Trigger FIRE_ONCE=TRUE');
  else
    dbms_output.put_line('Trigger FIRE_ONCE=FALSE');
  end if;
end;
/

Trigger FIRE_ONCE=FALSE

insert into SOURCE.T1
   values (6, 'Text 6');

commit;

select * 
  from DESTINATION.T1;
  2  
  ID TEXT
---- ----------
   1 Text 1
   2 Text 2
   3 Text 3
9999 Text 9999
   4 Text 4
   5 Text 5
   6 Text 6

select * 
  from DESTINATION.T1_X;

  ID TEXT
---- ----------
9999 Text 9999

No, Same Story! Actually, I had to recreate the trigger to make it work:

exec dbms_apply_adm.stop_apply('STREAMS_APPLY');
drop trigger destination.t1_x_trigger;

create or replace trigger destination.t1_x_trigger
 after insert on destination.T1 for each row 
  begin
    insert into T1_X(id, text)
      values (:new.id, :new.text);
  end;
/

begin
  DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY(
   trig_owner => 'DESTINATION', 
   trig_name  => 'T1_X_TRIGGER',
   fire_once  => false);
end;
/

set serveroutput on
begin
 if (dbms_ddl.IS_TRIGGER_FIRE_ONCE('DESTINATION', 'T1_X_TRIGGER'))
   then
    dbms_output.put_line('Trigger FIRE_ONCE=TRUE');
  else
    dbms_output.put_line('Trigger FIRE_ONCE=FALSE');
  end if;
end;
/

Trigger FIRE_ONCE=FALSE

exec dbms_apply_adm.start_apply('STREAMS_APPLY');

insert into SOURCE.T1
   values (7, 'Text 7');

commit;

select * 
  from DESTINATION.T1;

  ID TEXT
---- ----------
   1 Text 1
   2 Text 2
   3 Text 3
9999 Text 9999
   4 Text 4
   5 Text 5
   6 Text 6
   7 Text 7

select * 
  from DESTINATION.T1_X;

  ID TEXT
---- ----------
9999 Text 9999
   7 Text 7

Expected Behavior? Bug? I'm still split! What do you think? btw, don't forget to clean up your Streams configuration.