On Sep 17, 11:28 am, oracle <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I need to disable a trigger when running a stored procedure. When it
> finishes, trigger should be enable again.
>
> I try:
>
> BEGIN
> ALTER TRIGGER MyTrigger DISABLE;
>
> -- Some update code
>
> ALTER TRIGGER MyTrigger ENABLE;
> END ;
You cannot use DDL inside pl/sql blok or stored procedure.
You would have to use dynamic sql:
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER MyTrigger DISABLE';
...
EXECUTE IMMEDIATE 'ALTER TRIGGER MyTrigger ENABLE';
END;
But, I think this is realy bad idea,
unless you are sure you are the only user of this database.
As soon as you disable this trigger in your stored procedure
it will be 'inactive' for all other sessions.
What you really want to do is to disable this trigger only for your
session.
I may require to modify MyTrigger itself.
Step 1:
Create package MyPackage with static variable and set/get methods for
this variable.
Set default value of this variable to true.
Example: name the methods : setFlag/getFlag
Step 2:
Add to MyTrigger a condition depending on value of the variable from
package MyPackage.
If the value of this variable is set to false, don't execute content
of the trigger.
Example:
CREATE OR REPLACE TRIGGER MyTrigger
...
BEGIN
IF MyPackage.getFlag THEN
...
END IF;
END;
Step 3:
in your stored procedure call:
BEGIN
MyPackage.setFlag(false); -- "disable" trigger
...
MyPackage.setFlag(true); -- "enable" trigger
END;
HTH
Thomas
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
