Trigger Firing Order in Oracle

Trigger Firing Order in Oracle

When two or more triggers are defined for the same trigger event, in the past prior to 11g it was not possible to define the order in which those triggers are fired when the event occurs. Starting from 11g Oracle allows you to specify that a trigger must be fired only after another trigger for the same event is fired. It is done using FOLLOWS keyword followed by trigger name after which current trigger is to be invoked. We will create two tables as XX_TEST and XX_TEST_LOG as follows.

CREATE TABLE xx_test (n1 VARCHAR (5));

CREATE TABLE xx_test_log (n2 VARCHAR (100));

Create two triggers  as mentioned below, these should be fired before INSERT command of XX_TEST table for testing purpose.

CREATE OR REPLACE TRIGGER test_trigger1
   BEFORE INSERT
   ON xx_test
   FOR EACH ROW
BEGIN
   INSERT INTO xx_test_log
        VALUES ('Inside test_trigger1');
END;
/
CREATE  OR REPLACE TRIGGER test_trigger2
   BEFORE INSERT
   ON xx_test
   FOR EACH ROW
   FOLLOWS test_trigger1
BEGIN
   INSERT INTO xx_test_log
        VALUES ('Inside test_trigger2');
END;
/

In the above example, both TEST_TRIGGER1 and TEST_TRIGGER2 are associated with the same event as before insert of xx_test table. As we specified at the time of creating TEST_TRIGGER2 that it must be called only after TEST_TRIGGER1 is called using FOLLOWS keyword, it is always invoked after TEST_TRIGGER1. To test the order, let us insert a row into XX_TEST table and see what rows are placed in XX_TEST_LOG table.

-->SQL>  insert into xx_test values(100);
                 1 row created.

--> SQL> select  * from xx_test_log;

Query Output:-
--------------------------------------------------------------------------------------------
 N2
--------------------------------------------------
Inside test_trigger1
Inside test_trigger2