Triggers…



By Patch ~ May 1st, 2008. Filed under: Oracle, PL/SQL, SQL.

I ran into an issue at a customer site where certain triggers were disabled in the database where they should be enabled. It appeared that an update script, that is run every night, first disables all triggers on a couple of tables. Then does what it needs to do, without the overhead of the trigger code. and then, when it’s done, it enables all triggers again.

If the update code fails for whatever reason, then the re-enabling of the triggers is not performed, leaving the triggers in disabled state on the database, which can cause problems in everyday use. What you would actually want is a situation where you can disable the triggers, but just for the current session. Any other session should have the triggers enabled at all times. Unfortunately Oracle doesn’t support this kind of enabling/disabling of triggers. We do however have access to all the possibilities of PL/SQL in triggers so we can build a solution to this problem ourselves.

I have come up with a semaphore package which allows me to set and clear a flag or semaphore which I can then check in the trigger code and then, depending on the value of this semaphore execute or skip the code in the trigger. I have also used the knowledge I posted here about a boolean that’s really an integer.

The package

CREATE OR REPLACE PACKAGE bar_semaphore IS -- Author : Patrick Barel -- Public function and procedure declarations FUNCTION sem_emp RETURN BOOLEAN; PROCEDURE set_sem_emp; PROCEDURE clr_sem_emp; END bar_semaphore;

CREATE OR REPLACE PACKAGE BODY bar_semaphore IS -- Private variable declarations g_emp PLS_INTEGER; -- Function and procedure implementations PROCEDURE MINVALUE( p_variable_in_out IN OUT PLS_INTEGER , p_min_value_in IN PLS_INTEGER DEFAULT 0 ) IS BEGIN IF p_variable_in_out < p_min_value_in THEN p_variable_in_out := p_min_value_in; END IF; END MINVALUE; PROCEDURE initialization IS BEGIN g_emp := 0; END initialization; FUNCTION sem_emp RETURN BOOLEAN IS BEGIN RETURN( g_emp > 0 ); END sem_emp; PROCEDURE set_sem_emp IS BEGIN g_emp := g_emp + 1; END set_sem_emp; PROCEDURE clr_sem_emp IS BEGIN g_emp := g_emp - 1; MINVALUE( g_emp, 0 ); END clr_sem_emp; BEGIN initialization; END bar_semaphore;

The triggers

CREATE OR REPLACE TRIGGER tr_emp_bri BEFORE INSERT ON emp FOR EACH ROW DECLARE -- local variables here BEGIN IF NOT( bar_semaphore.sem_emp ) THEN IF :NEW.ename IS NULL THEN :NEW.ename := '<EMPTY>'; END IF; END IF; END tr_emp_bri;

CREATE OR REPLACE TRIGGER tr_emp_bru BEFORE UPDATE ON emp FOR EACH ROW DECLARE -- local variables here BEGIN IF NOT( bar_semaphore.sem_emp ) THEN IF :NEW.ename IS NULL THEN :NEW.ename := '<EMPTY>'; END IF; END IF; END tr_emp_bru;

The script

clear screen set serveroutput on select * from emp; update emp set ename = null where empno = 7934; select * from emp; rollback; select * from emp; exec bar_semaphore.set_sem_emp; update emp set ename = null where empno = 7934; select * from emp; rollback; exec bar_semaphore.clr_sem_emp; update emp set ename = null where empno = 7934; select * from emp; rollback; select * from emp; exec bar_semaphore.set_sem_emp; update emp set ename = null where empno = 7934; select * from emp; rollback; connect scott/tiger select * from emp; update emp set ename = null where empno = 7934; select * from emp;

The output of the script shows that when a new session is connected and the semaphore is set in the other session, disabling the triggers, this has no influence on the currently connected session. It is as if the semaphore is never set and the triggers function as normal. This way the triggers can be turned off for certain scripts, or pieces of code, that don’t want to be bothered by the code in the trigger, while other session still have this code in place and still have it turned on.

You can either choose to have a single semaphore in a package, or a single semaphore for every table or even a semaphores for different functions in the code. This way execution can be turned off and on exactly the way you want it.

Reader's Comments

  1. Pete Finnigan | May 1st, 2008 at 1:05 pm

    Hi,

    nice post and interesting. I have come across the same issue but from a security angle. I wanted to have triggers that would conditionally fire based on certain circumstances (user, group of users (role), time..). I came up with a similar solution but simpler. I used the “when” clause of the trigger to detect which user/role fired the trigger. in this way it was possible to control when the triggers fired. This meant triggers could always remain enabled but not fire for certain cionditions. I did some extensive testing and the performance “loss” due to the when clause was much less than executing checks in the body of the trigger. Running a trace shows that a loss less background work is done in the trigger. The losses i saw were shown at a high level in as a 3% impact for executing the when clause compared to 36% for executing the body. I wrote a presentation that is called “does vpd, fga and audit really cause a performance impact” - there is a link on my white papers page http://www.petefinnigan.com/orasec.htm including some sample code.

    Other areas we looked at were the OF clause as well.

    hth

    cheers

    Pete

  2. Michael A. Rife | May 1st, 2008 at 2:45 pm

    You could implement this with a CONTEXT variable as well. In the nightly job set the CONTEXT variable value. In the trigger check for the CONTEXT variable existence and value. CONTEXTs are used in Virtual Private Database (Fine Grain Access Control) in Oracle 9 and higher.

    http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php

    Mike

  3. Pete Finnigan | May 2nd, 2008 at 10:38 am

    Hi Mike,

    That’s an interesting comment and in fact is what i did also. I used a context to control whether the trigger fired or not. the context was set based on user / role / time etc. This was a neat way of doing it as we also used the same context to control FGA.

    The paper i mentioned in my last comment shows some sample code.

    cheers

    Pete

  4. Radoslav Golian | May 2nd, 2008 at 4:04 pm

    Interesting, but I think that using of semaphore is futile, because you have no shared resource (g_emp is local for every session). Semaphores are used in concurrent environment.

    I will use local application context or something like dbms_application_info.set_client_info to store this flag, not semaphore.

  5. Radoslav Golian | May 2nd, 2008 at 4:08 pm

    I’m sorry for the above comment. I have loaded this page without the other comments.

Leave a Comment