| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: can a trigger be disabled for a given session?
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
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
 |
 |