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

Hi Jerry F.,

> 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.
>
> 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?

For example, Oracle Replication option uses a package variable for that. You can set and reset it via DBMS_REPUTIL.REPLICATON_OFF and DBMS_REPUTIL.REPLICATON_ON. Or what we've done is a package called GLOBAL with public functions for global parameters (last_archive_date, local_database_name, etc.). The package has default body which loads the data from different sources (table parameters, system views, etc) into package variables. Some of the parameters are not stable during session, these have procedures to change them as well and these are loaded for every request.

So what! Once loaded and initialized the package stays in memory and offers the best performance one can have for globals.

• Sample IF global.audit_is_on THEN
  • do something END IF;

There are some restrictions though, but even this can be done. You are not allowed to select from the so called mutating table. This is the table that has the row-level trigger which initiated the active thread.

If table X has row update trigger x_tr and you update the table, then you are not allowed to select from table X during the run of the trigger (in the code it calls).

Regards,
Jan Received on Mon Aug 11 1997 - 00:00:00 CDT