This discussion is archived
1 Reply Latest reply: Jan 23, 2013 11:08 PM by Marwim RSS

Need to disable database trigger on tables via Oracle forms

986835 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points