Re: Disable triggers
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: joe_dba@xxxxxxxxxxx
- Date: Fri, 4 Jun 2010 17:50:04 +0100
Joe
One approach I have used in the past is to provide a "self-disabling"
function. But it comes with two costs.
Each trigger is instrumented so it checks a row in a table to see if it is
logically enabled. If it is, continue, if not, exit. You will want to hide
this lookup in a packaged function (and depending on your session pooling
strategy, you can then easily cache the value of the switch - either for the
life of the session, or until a cache timeout is reached.
CREATE OR REPLACE TRIGGER my_trigger
ON ....
IF mypackage.triggers_enabled THEN
-- do the work
END IF;
END;
/
You can make this a global switch (turn off every instrumented trigger) or
have multiple switches (per schema; per application; etc). For example, this
technique may be used to switch off some/all triggers while a bulk dataload
is taking place, based on the OS user, the program, the userid, etc.
Finally, your schema needs to be able to insert/update the data row; other
schemas need to be able to read the data row.
Advantages:
* No DDL required to disable/enable triggers
The costs:
1) the lookup (which hopefully is just once per session, not once per
trigger)
2) the development cost of making sure that every trigger is actually
instrumented (at least you can query the source and verify that your
function is called in every trigger)
Obviously this is only any use if all relevant trigger source code is under
your (company's) control in some sense (so don't try it on the several 10s
of 1000s of triggers in Oracle EBS, for example).
HTH
Regards Nigel
On 4 June 2010 17:28, Joe Smith <joe_dba@xxxxxxxxxxx> wrote:
> How to disable triggers on a table?
>
> I have multiple schemas and I want to create a procedure ( that when
> executed will disable/enable triggers ) that will be owned by one schema
> that can disable triggers on all the schemas. I want to do this without
> granting ALTER ANY TRIGGER. Is this possible?
>
>
>
Other related posts:
