Yup it is .... but I write so little actual code nowadays that I had forgotten about it ...
how quickly we get rusty On Thu, September 18, 2008 09:11, Thomas Olszewicki wrote: > > > 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 -~----------~----~----~----~------~----~------~--~---
