Oracle allows more than one trigger to be created for the same timing point, but it has never guaranteed the execution order of those triggers. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point. The following example creates a table with two triggers for the same timing point.

CREATE TABLE trigger_follows_test (
  id          NUMBER,
  description VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_1 – Executed’);
END;
/

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_2
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_2 – Executed’);
END;
/

If we insert into the test table, there is no guarantee of the execution order.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (1, ‘ONE’);
TRIGGER_FOLLOWS_TEST_TRG_1 – Executed
TRIGGER_FOLLOWS_TEST_TRG_2 – Executed

1 row created.

SQL>

We can specify that the TRIGGER_FOLLOWS_TEST_TRG_2 trigger should be executed before the TRIGGER_FOLLOWS_TEST_TRG_1 trigger by recreating the TRIGGER_FOLLOWS_TEST_TRG_1 trigger using the FOLLOWSclause.

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
FOLLOWS trigger_follows_test_trg_2
BEGIN
  DBMS_OUTPUT.put_line(‘TRIGGER_FOLLOWS_TEST_TRG_1 – Executed’);
END;
/

Now the TRIGGER_FOLLOWS_TEST_TRG_1 trigger always follows the TRIGGER_FOLLOWS_TEST_TRG_2 trigger.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (2, ‘TWO’);
TRIGGER_FOLLOWS_TEST_TRG_2 – Executed
TRIGGER_FOLLOWS_TEST_TRG_1 – Executed

1 row created.

SQL>

Don’t forget to clean up the test table.

DROP TABLE trigger_follows_test;

Thanks to Trigger Enhancements in Oracle 11g