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

 

COMPOUND Triggers in Oracle 11G

Let see how to use compound triggers in ORACLE 11G onwards...

Before start with compound trigger, first we will go through the existing trigger flow prior to ORACLE 11G.

Before 11G:
---------------

Create a test table and before statement, before row level, after row level, after statement triggers on the test table created.

SQL> create table triggertest_tab (col1 int);
Table created.
SQL> create or replace trigger triggertest_tab_before_stmt
  2  before insert on triggertest_tab
  3  begin
  4  dbms_output.put_line ('Message From before insert stmt level trigger');
  5  end;
  6  /
Trigger created.
SQL> create or replace trigger triggertest_tab_before_row
  2  before insert on triggertest_tab
  3  for each row
  4  begin
  5  dbms_output.put_line ('Message From before insert row level trigger');
  6  end;
  7  /
Trigger created.
SQL> create or replace trigger triggertest_tab_after_row
  2  after insert on triggertest_tab
  3  for each row
  4  begin
  5  dbms_output.put_line ('Message From after insert row level trigger');
  6  end;
  7  /
Trigger created.
SQL> create or replace trigger triggertest_tab_after_stmt
  2  after insert on triggertest_tab
  3  begin
  4  dbms_output.put_line ('Message From after insert stmt level trigger');
  5  end;
  6
  7  /
Trigger created.


Now, try insert a record into test table

SQL> set serveroutput on;

SQL> insert into triggertest_tab values(1);
Message From before insert stmt level trigger
Message From before insert row level trigger
Message From after insert row level trigger
Message From after insert stmt level trigger
1 row created.

This is how the existing functionality in prior to ORACLE 11G. We have to define triggers at various level separately.

From ORACLE 11G Onwards:
Now, From ORACLE 11G onwards, we can use compound trigger functionality to club one or more triggers into single compound trigger.


Lets create test table and write a compound trigger to have before statement, before row, after row, after statement triggers.

SQL> create table compound_test_tab (col1 int);

Table created.

SQL> create or replace trigger compound_trigger
  2  for insert
  3  on compound_test_tab
  4  compound trigger
  5      before statement is
  6          begin
  7              dbms_output.put_line ('Message From before insert stmt level trigger');
  8          end before statement;
  9      before each row is
 10          begin
 11              dbms_output.put_line ('Message From before insert row level trigger');
 12          end before each row;
 13      after each row is
 14          begin
 15              dbms_output.put_line ('Message From after insert row level trigger');
 16          end after each row;
 17      after statement is
 18          begin
 19              dbms_output.put_line ('Message From after insert stmt level trigger');
 20          end after statement;
 21  end compound_trigger;
 22  /
Trigger created.
SQL>
SQL>

Now, try insert into test table

SQL> insert into compound_test_tab values(1);
Message From before insert stmt level trigger
Message From before insert row level trigger
Message From after insert row level trigger
Message From after insert stmt level trigger
1 row created.

SQL>

This is how we can club multiple trigger levels into single compound trigger.

For more details, Refer Here