Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: can a trigger be disabled for a given session?

Re: can a trigger be disabled for a given session?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 02 Oct 1998 19:17:22 GMT
Message-ID: <363a2631.200001096@192.86.155.100>


A copy of this was sent to Nnoor_at_cris.com (NNOOR) (if that email address didn't require changing) On 02 Oct 1998 11:55:35 PDT, you wrote:

>
>A trigger exists on a table which records the user_id and time_stamp
>when ever any change is made to the data (insert, update, etc) for
>each row. Sometimes, a user can copy a whole bunch of data from one
>day to the next (e.g. copy of a schedule or time sheets, etc) and then
>a lot of processing is done to get the data ready for the next day.
>Problem is that all these operations cause the trigger to fire like
>mad which slows the whole process by a great deal. We can't just issue
>the ALTER TRIGGER DISABLE command because other users are using the
>system (and accessing the data in the same table---not the same data!)
>and we want to record who made changes to a given record and when. Is
>there any way to disable the trigger for a given session? What is the
>solution to such a problem if it can't be.
>
>Thanks for your help!
>
>Regards,
>Nasir.

it won't disable it but -- you can avoid doing the processing in the trigger.

Suppose you create a package such as:

create package my_globals
as

    g_dont_fire_trigger boolean default FALSE; end;
/

And then write your trigger as:

create trigger foo
after insert on foo
for each row
begin

   if ( my_globals.g_dont_fire_trigger ) then

      return;
   end if;
   .... normal code here ....
end;

Now, grant execute on the package MY_GLOBALS only to the user/users you want to be able to disable the trigger and they can just execute:

begin

   my_globals.g_dont_fire_trigger := TRUE; end;

to effectively stop it from processing. There will be some overhead as the trigger will fire but it won't do very much.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Oct 02 1998 - 14:17:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US