Disable Trigger
This Article will help you to understand “Disable Trigger” with examples and it’s detailed description.
By Default, trigger status is ENABLED.
Sometimes we will need to DISABLE trigger in some conditions like below.
- 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.
ALTER TRIGGER statement used to disable trigger.
Disable Trigger Example
For better understanding we will create table “sample_data”.
Then create trigger “trg_test_disable” on sample_data table.
We will check the trigger status from “USER_TRIGGERS” table. This table contains all details about triggers.
Then we will DISABLE trigger by ALTER TRIGGER statement and then check again the USER_TRIGGERS table and check the status of this trigger.
Code
|
0 1 2 3 4 5 6 7 8 |
--Creating sample_data table. CREATE TABLE sample_data ( id number(5) primary key, sample_result number(5) ); |
Output
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Creating TRIGGER SET SERVEROUTPUT ON; CREATE OR REPLACE TRIGGER trg_test_disable BEFORE UPDATE OR DELETE OR INSERT ON sample_data FOR EACH ROW BEGIN -- business logic will be here to perform on any insert/update/delete dbms_output.put_line('trg_test_disable Trigger called.'); END; / |
Output
To learn ENABLE trigger, Please refer ENABLE TRIGGER
|
0 1 2 |
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) = 'TRG_TEST_DISABLE'; |
| TRIGGER_NAME | STATUS |
|---|---|
| TRG_TEST_DISABLE | ENABLED |
|
0 1 2 3 4 |
--DISABLING TRIGGER ALTER TRIGGER trg_test_disable DISABLE; |
Output
Now again check the status of trigger.
|
0 1 2 |
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) = 'TRG_TEST_DISABLE'; |
| TRIGGER_NAME | STATUS |
|---|---|
| TRG_TEST_ENABLE | DISABLED |
