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!
17 Comments. Leave new
Nifty.
A huge security hole imho
Any logic behind having to pass TRUE to a paramater called ‘fire’, in order to disable firing the triggers?
Scary…
Thanks for sharing.Learned something new for today :)
Very interesting and useful information. Thanks for sharing!
What is DBMS_XSTREAM_GG? It isn’t documented. Can you produce at least a partial documentation for this?
Thanks for all the great comments.
@Laurent: Execution on dbms_xstream_gg requires an explicit grant, usually via DBMS_GOLDENGATE_AUTH, to run, so I don’t see this as a large security hole. I mentioned it on the related blog post https://www.pythian.com/news/30733/gg-heartbeat-suppresstriggers/ but neglected to mention it here.
@Toon: I agree, setting fire to FALSE to disable triggers would make a lot more sense senamtically!
@Mladen: As far as I know, this procedure is undocumented. It was designed for use internally by GoldenGate, but I believe it can be useful during testing where trigger firing must be avoided.
Very cool! Thanks for sharing.
Thats very very cool and that solvs my problem. 24 x 7 h Database with a lot of triggers and i had to update some fields in the database and was ready to rewrite my Triggers – uffff I saved a lot of time :-)
Unfortunately Oracle seems to have disabled this use in 11.2.0.4, and most likely 12.1 as well. Boo-Hiss! This is needed functionality for DBAs!
@Bryan very interesting… I’m seeing the same results. Expect a blog post about this soon.
So much easier in the old days – you just had to add the (undocumented) /*+ no_trigger */ hint to your SQL and the triggers wouldn’t fire. Good way to bypass home-grown audit triggers – but I’m not going to say which version it worked on.
Thanks for sharing
Very interesting. But you can not just completely disable the trigger, or use an IF condition in the body of the trigger, but also set that condition in the WHEN clause of the trigger header.
Hi,
Thank you for updating the original post. Thank you for showing the process.
This behaviour is documented. As a defect (read BUG)
Bug 16828566 : DBMS_XSTREAM_GG APIS SHOULD BE RESTRICTED TO GOLDENGATE PROCESSES
Bug 9236571 : DBMS_XSTREAM_GG.SET_FOO_TRIGGER_SESSION_CONTXT(TRUE) USER DML NOT FIRING TRIGGER