Disabling Oracle triggers on a per-session basis

Posted in: Technical Track

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!

email

Author

Interested in working with Marc? Schedule a tech call.

About the Author

Marc is a passionate and creative problem solver, drawing on deep understanding of the full enterprise application stack to identify the root cause of problems and to deploy sustainable solutions. Marc has a strong background in performance tuning and high availability, developing many of the tools and processes used to monitor and manage critical production databases at Pythian. He is proud to be the very first DataStax Platinum Certified Administrator for Apache Cassandra.

17 Comments. Leave new

Nifty.

Reply
Laurent Schneider
March 26, 2012 11:42 pm

A huge security hole imho

Reply
Toon Koppelaars
March 27, 2012 1:52 am

Any logic behind having to pass TRUE to a paramater called ‘fire’, in order to disable firing the triggers?

Reply
Dominic Brooks
March 27, 2012 3:03 am

Scary…

Reply

Thanks for sharing.Learned something new for today :)

Reply

Very interesting and useful information. Thanks for sharing!

Reply
Mladen Gogala
March 27, 2012 10:43 am

What is DBMS_XSTREAM_GG? It isn’t documented. Can you produce at least a partial documentation for this?

Reply
Marc Fielding
March 27, 2012 5:25 pm

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.

Reply

Very cool! Thanks for sharing.

Reply

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 :-)

Reply

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!

Reply
Marc Fielding
January 2, 2014 4:01 pm

@Bryan very interesting… I’m seeing the same results. Expect a blog post about this soon.

Reply
Jonathan Lewis
January 8, 2014 11:01 am

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.

Reply

Thanks for sharing

Reply

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.

Reply

Hi,

Thank you for updating the original post. Thank you for showing the process.

Reply

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

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *