Creating Trigger in ENABLE/DISABLE Mode in ORACLE 11G

Starting from ORACLE 11G, we can create triggers in DISABLE mode. Prior to ORACLE 11G, the triggers we create on table by default would be in ENABLE mode.

First, create a test table

create table enable_disable_test_tab (col1 int);

Now, create table in DISABLE mode as follows:

create trigger enable_disable_test_trg
before insert on
enable_disable_test_tab
for each row
disable
begin
dbms_output.put_line ('From enable_disable_test_trg trigger ');
end;
Now, create table in ENABLE mode as follows:

create trigger enable_disable_test_after_trg
after insert on
enable_disable_test_tab
for each row
enable
begin
dbms_output.put_line ('From enable_disable_test_after_trg trigger ');
end;


Now, lets query the DBA_TRIGGERS table to query the status of triggers.

select trigger_name, trigger_type, status
 from dba_triggers
where trigger_name in ( 'ENABLE_DISABLE_TEST_TRG', 'ENABLE_DISABLE_TEST_AFTER_TRG');

output:
-------


This is how we can create triggers in ENABLE/DISABLE mode.

Use the following command to enable trigger

alter trigger ENABLE_DISABLE_TEST_TRG ENABLE

Use the following command to disable the trigger

alter trigger ENABLE_DISABLE_TEST_TRG DISABLE

For more details, Refer Here