1 Reply Latest reply: Jan 24, 2013 1:08 AM by Marwim RSS

    Need to disable database trigger on tables via Oracle forms

    986835
      Need to disable a database trigger on tables via Oracle forms when on a click of a button irrespective of users (not admin)
        • 1. Re: Need to disable database trigger on tables via Oracle forms
          Marwim
          Hello,

          welcome to the forum.
          Need to disable a database trigger on tables
          You can use
          EXECUTE IMMEDIATE 'ALTER TRIGGER <trigger_name> DISABLE '
          But before you do it you should ask
          * why it is necessary to disable the trigger?
          * how can you guarantee that the trigger will be enabled when your transaction is finished?

          A better solution might be to set a flag, for example a global variable in a package. When the trigger fires it reads the value of the flag and depending on the value it will execute some code or not.
          PACKAGE global_vars
          ...
          g_allow_insert   BOOLEAN := FALSE;
          
          g_insert_not_allowed EXCEPTION;
          TRIGGER on some_table
          
          IF NOT global_vars.g_allow_insert THEN
              RAISE global_vars.g_insert_not_allowed;
          END IF;
          Now in your forms you can set global_vars.g_allow_insert = TRUE and the trigger will be "disabled".
          The trigger will be "enabled" for other sessions and therefore if your forms session is interrupted, you don't have to muse about how to enable the trigger.

          Regards
          Marcus