Useful shortcuts for vi editor

Wednesday, 25 October 2017

How to disable/enable all triggers for tables?

Disable triggers for a table
SQL> alter table HR.TRG_EMPLOYEES disable all triggers;

Enable triggers for a table
SQL> alter table HR.TRG_EMPLOYEES enable all triggers;

Disable triggers belong to a schema (e.g. HR)
SQL>
DECLARE
   STR_EXEC   VARCHAR2 (1000);
BEGIN
   FOR cur_node IN (SELECT owner, table_name
                      FROM all_triggers
                     WHERE owner = 'HR')
   LOOP
      BEGIN
         str_exec :=
               'alter table '
            || cur_node.owner
            || '.'
            || cur_node.table_name
            || ' DISABLE ALL TRIGGERS';
         DBMS_OUTPUT.put_line (str_exec);

         EXECUTE IMMEDIATE str_exec;
      END;
   END LOOP;
END;
/

Enable triggers belong to a schema (e.g. HR)
SQL>
DECLARE
   STR_EXEC   VARCHAR2 (1000);
BEGIN
   FOR cur_node IN (SELECT owner, table_name
                      FROM all_triggers
                     WHERE owner = 'HR')
   LOOP
      BEGIN
         str_exec :=
               'alter table '
            || cur_node.owner
            || '.'
            || cur_node.table_name
            || ' ENABLE ALL TRIGGERS';
         DBMS_OUTPUT.put_line (str_exec);

         EXECUTE IMMEDIATE str_exec;
      END;
   END LOOP;
END;
/