Trigger Execution Order in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Trigger Execution Order in Oracle using Follows Clause

In this article, I am going to discuss How to Manage Trigger Execution Orders in Oracle using Follows Clause with Examples. Please read our previous article where we discussed Instead of Triggers (Views) in Oracle with Examples.

How to Manage Trigger Execution Order in Oracle using Follows Clause

Let us take an example and see how the trigger works.

Trigger 1:

Create or replace trigger test1
Before insert
On emp
Begin
Insert into which_fired_first values (s1.nextval,’test1’);
End;

Trigger 2:

Create or replace trigger test1
Before insert
On emp
Begin
Insert into which_fired_first values (s1.nextval,’test2’);
End;

We have created two triggers test1 and test2. The test1 trigger will execute when there is a before insert event happens on the table emp. The trigger will store the value in the which_fired_first table. This is the same case with the test2 trigger. Both these triggers have the event as before insert on the table emp. We might not know which trigger executes first. We cannot guarantee because both are on the same level.

If in case, we need order in the triggers to be executed, then there is a clause called follows. Let us go ahead and explain the clause “follows” with an example.

Step 1: Create a table.

In order to learn about the “follows” clause, let us go ahead and create a table emp by executing the following Create Table Statement.

Create table emp
(
id number,
Name varcha2(100)
);

So, the table is created. Now, let us go ahead and create another table where the trigger saves values. We will be creating a table with the name which_fired_first by executing the following Create Table Statement.

Create table which_fired_first
(
seq number,
Trigger_name varchar2(100)
);

So, the which_fired_first table is also created.

Step 2: Create a Sequence in Oracle

In the previous articles, we created a sequence in order to find out the trigger sequence. We will do a similar way to find out which trigger is executed first. Let us go ahead and create a sequence by executing the following SQL statement.

create sequence s1;

So, the sequence is created.

Step 3: Create Triggers Without Follows Clause

Let us go ahead and create two triggers without the “follows” clause and test the triggers and then we will check which trigger is executed first.

Trigger 1:

Create or replace trigger test1
Before insert
On emp
Begin
Insert into which_fired_first values (s1.nextval,’test1’);
End;

We have the trigger test1. This trigger will insert the sequence to the table which_fired_first. Let us go ahead and compile this trigger.

Trigger 2:

Create or replace trigger test1
Before insert
On emp
Begin
Insert into which_fired_first values (s1.nextval,’test2’);
End;

This trigger also inserts the value to the table which_fired_first. Let us go ahead and compile this trigger.

You can see that both triggers are created.

Step 4: Test the Trigger.

Let us go ahead and insert a record into the table emp by executing the following INSERT SQL Statement. When we insert any data into the table emp then the trigger will be executed and the sequence will be inserted into the table which_fired_first.

Insert into emp values (1,’abc’);

You can see a record is inserted into the table emp. Let us go ahead and check the table which_fired_first to see which trigger is executed first by executing the following SQL Statement.

select * from which_fired_first order by seq;

You can see the output which shows a trigger test2 with seq 1. The other trigger test1 with the seq 2. So, as per the output, the test2 trigger is executed first. We cannot determine which trigger will execute first. Sometimes test2 will execute first and sometimes test1 trigger will execute first.

In order to overcome this problem, we will use the clause “follows”. Before using this clause let us delete the data in the table which_fired_first by executing the below SQL Statement.

delete from which_fired_first;

We have deleted the data in the table which_fired_first.

Step 5: Create a trigger with the “follows” clause in Oracle

Now, we will use the clause “follows” in trigger test2 because trigger test2 should execute only after trigger test1. Let us go ahead and include the clause executing the below statement.

Create or replace trigger test1
Before insert
On emp
Follows test1
Begin
Insert into which_fired_first values (s1.nextval,’test2’);
End;

So, now the trigger test2 will execute only after the trigger test1 is executed. Let us go ahead and execute the trigger test2.

Let us go ahead and insert a record in the table emp again by executing the below insert SQL Statement and see if it works or not.

insert into emp (1, ‘abc’);

So, the record is inserted into the table emp. Let us go ahead and check the table which_fired_first and see the sequence by executing the below SELECT SQL Statement.

select * from which_fired_first order by seq;

From that output, you can see that seq 3 is test1 and seq 4 is test2. That means test1 is executed first.

Note: If the trigger execution order is important then only, we need to use the clause “follows”. So, this is how we can use the clause “follows” to maintain a certain order of executing the triggers.

In the next article, I am going to discuss Compound Triggers in Oracle with Examples. Here, in this article, I try to explain How to Manage Trigger Execution Orders in Oracle using Follows Clause with Examples. I hope you enjoy this How to Manage Trigger Execution Order in Oracle using Follows Clause article.