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!