Skip to content

Insight and analysis of technology and business strategy

Disabling Oracle triggers on a per-session basis

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

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 or, 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
        dbms_output.put_line('Trigger body');
  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!

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!