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!
Share this
You May Also Like
These Related Stories
No Comments Yet
Let us know what you think