Oracle PL/SQL – Enable and Disable Triggers

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.

--Creating test_data table.
CREATE TABLE test_data
  id number(5) primary key,
  test_result number(5)
);

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;

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');

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';

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';

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');

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');

References

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