How to Enable or Disable All Triggers in a Oracle Schema - Script

advertisements

_____________________________________________________________________________________________________________________

To disable all triggers in an Oracle schema, you can use the following SQL script. This script generates the necessary SQL statements to disable all triggers and then executes them. Disabling triggers can be useful in scenarios where you need to temporarily suspend their execution, such as during data maintenance, bulk loading, or troubleshooting activities. Disabling triggers can prevent them from firing and affecting the normal operation of the database. It's important to remember that disabling triggers should be done with caution, as it can impact the expected behavior of the database. Be sure to re-enable the triggers after completing the required tasks.

advertisements

 

Disable Script

DECLARE
  sql_stmt VARCHAR2(1000);
BEGIN
  FOR rec IN (SELECT trigger_name FROM all_triggers WHERE owner = 'YOUR_SCHEMA_NAME') LOOP
    sql_stmt := 'ALTER TRIGGER ' || rec.trigger_name || ' DISABLE';
    EXECUTE IMMEDIATE sql_stmt;
    DBMS_OUTPUT.PUT_LINE('Trigger ' || rec.trigger_name || ' disabled');
  END LOOP;
END;
/

Enable Script

DECLARE
  sql_stmt VARCHAR2(1000);
BEGIN
  FOR rec IN (SELECT trigger_name FROM all_triggers WHERE owner = 'YOUR_SCHEMA_NAME') LOOP
    sql_stmt := 'ALTER TRIGGER ' || rec.trigger_name || ' ENABLE';
    EXECUTE IMMEDIATE sql_stmt;
    DBMS_OUTPUT.PUT_LINE('Trigger ' || rec.trigger_name || ' enabled');
  END LOOP;
END;
/

Replace 'YOUR_SCHEMA_NAME' with the name of the schema for which you want to disable triggers. Explanation: The script queries the all_triggers view to retrieve the names of all triggers in the specified schema. Here's what the script does: trigger_name: This column represents the name of each trigger. The script then generates and executes an ALTER TRIGGER statement for each trigger to disable it.

Sample Output

DECLARE
  sql_stmt VARCHAR2(1000);
BEGIN
  FOR rec IN (SELECT trigger_name FROM all_triggers WHERE owner = 'YOUR_SCHEMA_NAME') LOOP
    sql_stmt := 'ALTER TRIGGER ' || rec.trigger_name || ' DISABLE';
    EXECUTE IMMEDIATE sql_stmt;
    DBMS_OUTPUT.PUT_LINE('Trigger ' || rec.trigger_name || ' disabled');
  END LOOP;
END;
/ 

PL/SQL procedure successfully completed.

In this sample output, you see messages indicating that the specified triggers have been disabled. Please ensure you have the necessary privileges to query the all_triggers view and execute the ALTER TRIGGER statement. Test the script in a controlled environment before running it in a production database. Also, remember to re-enable the triggers after completing the required tasks.

_____________________________________________________________________________________________________________________

Website Stats