How to disable all triggers that concerns a table in Oracle?

Question

In Postgresql, if I do ALTER TABLE mytable DISBLE TRIGGERS ALL, all triggers and constraints regarding this table are suspended.

Especially, foreign keys from other tables to mytable are suspended, and I can delete from mytable without problem. I have the risk to break database consistency, but I know what I'm doing, and I must have superuser privileges.

How do I do the same in Oracle ? I am under the impression that ALTER TABLE mytable DISBLE ALL TRIGGERS in Oracle will suspend all trigger and constraints belonging to mytable, but not those that concerns mytable but belong to other tables (especially foreign keys).

Am I right and what would be the way to achieve the same result as in Postgresql in Oracle ?

Answer 1

The syntax does disable triggers in Oracle:

SQL> select trigger_name, status from user_triggers
  2  where table_name='TEST'
  3  /

TRIGGER_NAME                   STATUS
------------------------------ --------
TEST_TRIGGER                   ENABLED

SQL> ALTER TABLE test DISABLE ALL TRIGGERS
  2  /

Table altered.

SQL> select trigger_name, status from user_triggers
  2  where table_name='TEST'
  3  /

TRIGGER_NAME                   STATUS
------------------------------ --------
TEST_TRIGGER                   DISABLED

SQL>

However it won't do anything for foreign keys, or indeed any other constraint. That's because Oracle doesn't use triggers to enforce such things. Okay, under the covers constraints and user-defined triggers may share certain low-level kernel code. But at the level we're talking they are two different things.

If you want to disable all foreign keys on a table I'm afraid you'll need to use something like this:

SQL> select constraint_name, status from user_constraints
  2  where table_name = 'EMP'
  3  and constraint_type = 'R'
  4  /

CONSTRAINT_NAME                STATUS
------------------------------ --------
FK_DEPTNO                      ENABLED


SQL> begin
  2      for r in ( select constraint_name, status from user_constraints
  3                 where table_name = 'EMP'
  4                 and constraint_type = 'R' )
  5      loop
  6          execute immediate 'alter table emp disable constraint '||r.constraint_name;
  7      end loop;
  8* end;
  9  /

PL/SQL procedure successfully completed.

SQL> select constraint_name, status from user_constraints
  2  where table_name = 'EMP'
  3  and constraint_type = 'R'
  4  /

CONSTRAINT_NAME                STATUS
------------------------------ --------
FK_DEPTNO                      DISABLED

SQL>

This is the sort of thing you'll probably want to wrap in a user-defined function, which takes TABLE_NAME as a parameter. Also you'll need a similar function to re-enable the constraints.