Disabling Oracle triggers on a per-session basis

Update Jan-2014: I’ve added a note on disabling triggers in Oracle 11.2.0.4

I recently blogged about an issue getting GoldenGate heartbeats to work with SUPPRESSTRIGGERS, and I thought the findings would be of interest beyond the GoldenGate community. In short, GoldenGate can suppress trigger execution, and does this using the dbms_xstream_gg.set_foo_trigger_session_contxt procedure.

Previously, to suppress trigger execution, you had a choice: either turn it off system-wide with ALTER TRIGGER xxx DISABLE, or to add an IF statement to the trigger code, bypassing execution if a certain value (say, from USERENV or a global variable). There are situations where it may not be possible to modify trigger code (for a third-party application, for example) and where a trigger cannot be disabled system-wide. This happens in replication environment like GoldenGate, but could also be desirable for monitoring checks or code testing.

This method of disabling triggers requires Oracle 11.2.0.2+ or 10.2.0.5+, plus execute permissions on sys.dbms_xstream_gg. Since it’s a call to an XStream package, it may also require a XStream license.

So if I haven’t scared you away yet, here’s a quick testcase:

SQL> create table trigger_test as select * from dual;
Table created.
SQL> create or replace trigger trigger_test_aiu
after insert or update on trigger_test
begin
        dbms_output.put_line('Trigger body');
end;
/
  2    3    4    5
Trigger created.
SQL> set serveroutput on
SQL> insert into trigger_test select * from dual;
Trigger body
1 row created.

So we can see that the text “Trigger body” is printed every time trigger_test is inserted into. Now trying a call to set_foo_trigger_session_contxt:

SQL> exec sys.dbms_xstream_gg.set_foo_trigger_session_contxt(fire=>true);
PL/SQL procedure successfully completed.
SQL> insert into trigger_test select * from dual;
1 row created.

Note that we don’t see “Trigger body” anymore. Look ma, no triggers!