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