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 - --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
