Friday, November 30, 2007

How to disable an Oracle trigger for a session...

Have I mentioned that I hate triggers?????




I'm unloading a ton of data from a database and can't stand the fact that the damn triggers are firing off doing their thing.

My first thought was to unload as a different userid, and tweek the trigger to ignore that userid's transactions (do nothing when the user is the UNLOAD userid). I was just about to go with this approach and I then found the following link by Tom Kyte.

so...steps to do this are as follows (dumbed down from Mr. Kyte's explanation):
1) tell everyone you hate triggers and complain alot
2) as the owner of the table, create a package like the following:

CREATE PACKAGE globals_pkg
IS
gv_run_my_trigger BOOLEAN := TRUE;
END;
/


3) add the following to the beginning of the body of the trigger (a.k.a. just after the BEGIN statement)

IF NOT globals_pkg.gv_run_my_trigger
THEN RETURN;
END IF;

4) tell everyone you hate triggers
5) run the following in your SQL*Plus session that you'd like to "disable" the trigger in

BEGIN
globals_pkg.gv_run_my_trigger := FALSE;
END;
/

Special notes:
Note 1) This will not disable the trigger. The trigger will fire but none of the logic in the trigger will fire in your session anymore UNTIL you set that global variable in the globals_pkg back to TRUE. Yes there's overhead that the trigger is still firing, but it won't do any work so it's effectively shut off (for now).
Note 2) all the other sessions connected to the database WILL NOT be impacted by you setting the value of that variable to FALSE.

I ran a test...

Session one:

SQL> create or replace package globals_pkg
2 IS
3
4 gv_my_string VARCHAR2(10) := 'RICH';
5 END;
6 /

Package created.

SQL> execute dbms_output.put_line(globals_pkg.gv_my_string);
RICH

PL/SQL procedure successfully completed.

SQL> begin globals_pkg.gv_my_string := 'TINA'; END;
2 /

PL/SQL procedure successfully completed.

SQL> execute dbms_output.put_line(globals_pkg.gv_my_string);
TINA

PL/SQL procedure successfully completed.



Session two:

SQL> execute dbms_output.put_line(globals_pkg.gv_my_string);
RICH

PL/SQL procedure successfully completed.



Until next time...Rich

No comments: