Systems Engineering and RDBMS

Setting the execution order of Triggers in Oracle 11g

Posted by decipherinfosys on February 27, 2008

We are all aware that we can write multiple triggers on a single table based on the business requirement(s). One can have ‘BEFORE INSERT’ trigger on the table to generate a running number and on the same table there can be an ‘AFTER UPDATE’ trigger to calculate new value for another column. We can even define more than one trigger of the same type i.e. multiple ‘AFTER UPDATE’ triggers or multiple ‘BEFORE UPDATE’ triggers.  Normally execution order is determined by the type of the trigger. Oracle executes all triggers of the same type before executing trigger of different type. But what is the order of execution in case when there are multiple triggers of the same type? How do we know which trigger will be executed first?

Prior to 11g, there was no sure answer for this type of situation. Any one trigger (if they are of the same type) can get executed first and there is no guarantee that triggers will be executed again in the same order. This can create issues with the  data. In 11g, Oracle introduced ‘FOLLOWS’ clause to control the execution order of the triggers when they are of the same type.

Connect to SQL*Plus or SQL*Developer and create following table and sequence.

CREATE TABLE TEST
(
COL1 NUMBER(9),
COL2 NUMBER(9),
COL3 DATE
)
/

CREATE SEQUENCE TEST_SEQ START WITH 1234;

Now we will create two ‘BEFORE INSERT’ trigger on the table.

CREATE OR REPLACE TRIGGER TRI_TEST_1
BEFORE INSERT
ON TEST
FOR EACH ROW
BEGIN
:NEW.COL1 := TEST_SEQ.NEXTVAL;
Dbms_output.put_line(‘In Trigger TRI_TEST_1’);
END;
/

CREATE OR REPLACE TRIGGER TRI_TEST_2
BEFORE INSERT
ON TEST
FOR EACH ROW
DECLARE
v_col2 VARCHAR2(10);
BEGIN
SELECT REVERSE(to_char(:NEW.COL1))
INTO v_col2
FROM DUAL;
:NEW.COL2 := to_number(v_col2); –REVERSE(:NEW.COL1);
Dbms_output.put_line(‘In Trigger TRI_TEST_2’);
END;
/

Now let us insert the record in the table and examine the result.

SQL> INSERT INTO TEST(COL3) VALUES(SYSDATE);

Following is the result.

In Trigger TRI_TEST_2
In Trigger TRI_TEST_1

1 row created.

SQL> SELECT * FROM TEST;

COL1  COL2  COL3
—– —– ———
1234        26-FEB-08

It is very clear from the result that trigger TRI_TEST_2 got fired first. As a result COL2 value remained null because value  for COL1 is not populated at this time. Thus unordered execution of the trigger can lead to wrong results. To avoid this we can use ‘FOLLOWS’ clause.

Let us rewrite the TRI_TEST_2 trigger so that it gets executed after execution of trigger TRI_TEST_1. Here is the revised code.

CREATE OR REPLACE TRIGGER TRI_TEST_2
BEFORE INSERT
ON TEST
FOR EACH ROW
FOLLOWS TRI_TEST_1
DECLARE
v_col2 VARCHAR2(10);
BEGIN
SELECT REVERSE(to_char(:NEW.COL1))
INTO v_col2
FROM DUAL;
:NEW.COL2 := to_number(v_col2);
Dbms_output.put_line(‘In Trigger TRI_TEST_2’);
END;
/

After re-creating trigger, again insert record into the table using previously used insert statement and check result.

SQL> SELECT * FROM TEST;

COL1  COL2  COL3
—– —– ———
1235    5321    26-FEB-08

Now, we see that COL2 is correctly populated as execution of  trigger TRI_TEST_2 followed the execution of trigger TRI_TEST_1.

It is very clear from the example above that if we have multiple triggers of the same type on the same table, Oracle executes it randomly unless ‘FOLLOWS’ clause is specified for ordered execution of triggers.  Same functionality is available in SQL Server as well and we will blog that tomorrow.

2 Responses to “Setting the execution order of Triggers in Oracle 11g”

  1. […] Setting the execution order of Triggers in Oracle 11g […]

  2. […] If multiple triggers are of the same type, then they follow above order but there is no guarantee which one will be executed first unless it is created with ‘FOLLOWS’ clause. This clause is introduced in 11g. To know more about this clause, please visit our previous blog post. […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: