Disable ALL Trigger
“Disable ALL Trigger” Article will help you to understand “Disable ALL Triggers of the table” with examples and it’s detailed description.
ALTER TABLE statement used to disable all triggers of the table.
Disable ALL Trigger Example
For better understanding we will create table “sample_users”.
Then create two triggers “trg_test_disable_all_one” and “trg_test_disable_all_two” on sample_users table.
We will check the trigger status from “USER_TRIGGERS” table. This table contains all details about triggers.
Then we will DISABLE triggers by ALTER TABLE statement and then check again the USER_TRIGGERS table and check the status of this triggers.
Code
|
0 1 2 3 4 5 6 7 8 |
--Creating sample_users table. CREATE TABLE sample_users ( user_id number(5) primary key, user_name varchar2(20) ); |
Output
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- Creating TRIGGER SET SERVEROUTPUT ON; CREATE OR REPLACE TRIGGER trg_test_disable_all_one BEFORE UPDATE OR DELETE OR INSERT ON sample_users FOR EACH ROW BEGIN -- business logic will be here to perform on any insert/update/delete dbms_output.put_line('trg_test_disable_all_one Trigger called.'); END; / CREATE OR REPLACE TRIGGER trg_test_disable_all_two BEFORE UPDATE OR DELETE OR INSERT ON sample_users FOR EACH ROW BEGIN -- business logic will be here to perform on any insert/update/delete dbms_output.put_line('trg_test_disable_all_two Trigger called.'); END; / |
Output
To learn ENABLE ALL Trigger, Please refer ENABLE ALL TRIGGER
|
0 1 2 |
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) in ('TRG_TEST_DISABLE_ALL_ONE','TRG_TEST_DISABLE_ALL_TWO'); |
| TRIGGER_NAME | STATUS |
|---|---|
| TRG_TEST_DISABLE_ALL_TWO | ENABLED |
| TRG_TEST_DISABLE_ALL_ONE | ENABLED |
|
0 1 2 3 4 |
--DISABLING ALL TRIGGERS of sample_users ALTER TABLE sample_users DISABLE ALL TRIGGERS; |
Output
Now again check the status of triggers.
|
0 1 2 |
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS where upper(TRIGGER_NAME) in ('TRG_TEST_DISABLE_ALL_ONE','TRG_TEST_DISABLE_ALL_TWO'); |
| TRIGGER_NAME | STATUS |
|---|---|
| TRG_TEST_DISABLE_ALL_TWO | DISABLED |
| TRG_TEST_DISABLE_ALL_ONE | DISABLED |
