Re: turning off triggers or allowing a trigger to determine to run or exit?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/11
Message-ID: <33ef2c00.6047525@newshost>#1/1

2 very easy ways to implement this

1.) if the only thing the triggers do is audit, then "alter trigger TRIGGER_NAME disable" would turn off auditing. You can re-enable auditing at any time by "alter trigger trigger_name enable".

2.) store 'audit configuration' in a table. Your database people aren't correct when they say oracle won't let you read from a table during a trigger. It won't let you read the table that Fired the trigger in a Row level trigger, but you can read from other tables. I would create a package that looks like:

create table audit_setting_tbl ( should_audit char(1) ); insert into audit_setting_tbl values ('Y');

create package audit_setting
as

        should_audit boolean default TRUE;
end;
/
create package body audit_setting
as

begin

    for x in ( select * from audit_setting_tbl ) loop

       if (x.should_audit = 'Y' ) then should_audit := TRUE;
       else                            should_audit := FALSE;
       end if;

    end loop;
end;
/

Then, in the triggers code something like:

   ...
   if ( audit_settting.should_audit ) then

      AUDIT_CODE HERE.
   end if;
   ...

this boolean variable will set itself the first time it is accessed (you don't need to 'initialize' it, it'll just happen).

On 11 Aug 1997 03:39:32 GMT, jfdecd_at_execpc.com (Jerry F. Davis) wrote:

>I am *not* a Database person, but I am submitting a question for one who is in
>our company.
>
>We have a little problem. We have a product that we have developed that has
>something in it called an audit trail. The database person says it is all
>implemented via triggers.
>
>What we want to do is to offer this audit trail feature as an option.
>
>Current thinking is to have two identical databases with one using the
>triggers for audit trail and one without.
>
>This seems a waste to me. Is there a way we can set a variable somewhere in
>the database that we could then look at during an audit trail trigger event
>that we could simply exit the trigger if the audit trail flag variable is not
>set?
>
>Our database people don't think so. They say Oracle won't allow them to read
>a variable from a table during a trigger event - or something to that effect.
>
>I hope I have stated the case properly for them!!!
>Has anyone ever done this?
>
>Thank you.
>
>Regards,
>Jerry

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 11 1997 - 00:00:00 CDT