This article shows you how to use ALTER TRIGGER and ALTER TABLE to enable and disable triggers.


-- enable / disable a trigger
ALTER TRIGGER trigger_name ENABLE;
ALTER TRIGGER trigger_name DISABLE;

-- enable / disable all triggers for a specific table
ALTER TABLE table_name ENABLE ALL TRIGGERS;
ALTER TABLE table_name DISABLE ALL TRIGGERS;

1. Table + Triggers

1.1 Create a table and two triggers on it.

test_data

--Creating test_data table.

CREATE TABLE test_data
(
  id number(5) primary key,
  test_result number(5)
);
trg_test_a

SET SERVEROUTPUT ON;

CREATE OR REPLACE TRIGGER trg_test_a
BEFORE
     UPDATE OR DELETE OR INSERT
ON test_data
  FOR EACH ROW 

BEGIN

  -- business logic will be here to perform on any insert/update/delete
  dbms_output.put_line('trg_test_a Trigger called.');
  
END;
trg_test_b

SET SERVEROUTPUT ON;

CREATE OR REPLACE TRIGGER trg_test_b
BEFORE
     UPDATE OR DELETE OR INSERT
ON test_data
  FOR EACH ROW 

BEGIN

  dbms_output.put_line('trg_test_b Trigger called.');
  
END;

When you create triggers, they will be ENABLED by default.


SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) in ('TRG_TEST_A','TRG_TEST_B');
TRIGGER_NAME STATUS
trg_test_a ENABLED
trg_test_b ENABLED

2. ALTER TRIGGER – Enable and disable a Trigger

2.1 Example to disable a trigger trg_test_a


ALTER TRIGGER trg_test_a DISABLE;

2.2 Check trigger status.


SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) = 'TRG_TEST_A';
TRIGGER_NAME STATUS
TRG_TEST_A DISABLED
Note
Sometimes you will need to DISABLE trigger in some conditions like,

  1. An object that the trigger references is not available.
  2. You must perform a large data load and want it to proceed quickly without firing triggers.
  3. You are loading data into the table to which the trigger applies.

2.3 Example to enable a disabled trigger trg_test_a


ALTER TRIGGER trg_test_a ENABLE;

2.4 Check trigger status again.


SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) = 'TRG_TEST_A';
TRIGGER_NAME STATUS
TRG_TEST_A ENABLED

3. ALTER TABLE – Enable and disable all Triggers

3.1 Example to disbale all triggers for a specific table test_data


ALTER TABLE test_data DISABLE ALL TRIGGERS;

3.2 Check triggers status.


SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) IN ('TRG_TEST_A','TRG_TEST_B');
TRIGGER_NAME STATUS
TRG_TEST_A DISABLED
TRG_TEST_B DISABLED

3.3 Example to enable all triggers for a specific table test_data


ALTER TABLE test_data ENABLE ALL TRIGGERS;

3.4 Check triggers status again.


SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) IN ('TRG_TEST_A','TRG_TEST_B');
TRIGGER_NAME STATUS
TRG_TEST_A ENABLED
TRG_TEST_B ENABLED

References

  1. ALTER TRIGGER examples
  2. Database administrator’s Guide:- Enabling and Disabling Triggers
  3. PL/SQL Triggers