Just a caveat that it is easy to get into the habit of doing something "the simple way" only to be bitten in the butt by it that one time that your assumption isnt correct. Not that it has ever happened to me of course .. cough cough
But you know your environment better than I do :-) On Sep 18, 9:37 am, oracle <[EMAIL PROTECTED]> wrote: > Thank you folks > > I feel the idea what Thomas suggested will serve my purpose .Because I > need to do the update for an upgrade so I will be the only user :-) . > > Let us make it simple > > On Sep 18, 9:27 am, "Rob Wolfe" <[EMAIL PROTECTED]> wrote: > > > > > 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- Hide quoted text - > > > - Show quoted text -- Hide quoted text - > > - Show quoted text - --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
