On Sep 17, 4:30 pm, "Rob Wolfe" <[EMAIL PROTECTED]> wrote:
> On Wed, September 17, 2008 15:34, Thomas Olszewicki wrote:
>
> > 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
>
> Thomas that is a terrific solution!- Hide quoted text -
>
> - Show quoted text -
Rob,
"terrific", thx
Isn't this model used widely?
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 Oracle-PLSQL@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
