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.
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,
Sometimes you will need to DISABLE trigger in some conditions like,
- An object that the trigger references is not available.
- You must perform a large data load and want it to proceed quickly without firing triggers.
- 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 |
really helpful