Understanding FOLLOWS Clause In Oracle Database

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. Internally Oracle decides the order of execution of those triggers.

SQL> CREATE TABLE incremented_values
  2  (
  3   value_inserted    NUMBER,
  4   value_incremented NUMBER
  5  );

Table created.

SQL> CREATE OR REPLACE TRIGGER increment_by_one
  2  BEFORE INSERT ON incremented_values
  3  FOR EACH ROW
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE('Firing Trigger : increment_by_one');
  6    :NEW.value_incremented := :NEW.value_incremented + 1;
  7  END;
  8  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER increment_by_two
  2  BEFORE INSERT ON incremented_values
  3  FOR EACH ROW
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE('Firing Trigger : increment_by_two');
  6    IF :NEW.value_incremented > 1 THEN
  7      :NEW.value_incremented := :NEW.value_incremented + 2;
  8    END IF;
  9  END;
 10  /

Trigger created.

SQL> SELECT * FROM incremented_values;

no rows selected

SQL> INSERT INTO incremented_values VALUES(1,1);
Firing Trigger : increment_by_two
Firing Trigger : increment_by_one

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM incremented_values;

VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
             1                 2
			 
SQL> INSERT INTO incremented_values VALUES(1,1);
Firing Trigger : increment_by_two
Firing Trigger : increment_by_one

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM incremented_values;

VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
             1                 2
             1	               2

The increment_by_two trigger fired first and did nothing because the value_incremented column was not greater than 1; then the increment_by_one trigger fired to increase the value_incremented column by 1. Is this the result you will receive? The preceding example offers no guarantee. Will this always be the result on my database? Again, there is no guarantee. Prior to Oracle Database 11g, Oracle explicitly stated that there was no way to control or assure the order in which multiple triggers of the same type on a single table would fire. There are many theories, the most prevalent being that triggers fire in reverse order of creation or by order of object ID—but even those theories cannot be relied upon.

Starting with Oracle Database 11g, however, you can guarantee the firing order using the FOLLOWS clause.

SQL> CREATE OR REPLACE TRIGGER increment_by_one
  2  BEFORE INSERT ON incremented_values
  3  FOR EACH ROW
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE('Firing Trigger : increment_by_one');
  6    :new.value_incremented := :new.value_incremented + 1;
  7  END;
  8  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER increment_by_two
  2  BEFORE INSERT ON incremented_values
  3  FOR EACH ROW
  4  FOLLOWS increment_by_one
  5  BEGIN
  6    DBMS_OUTPUT.PUT_LINE('Firing Trigger : increment_by_two');
  7    IF :new.value_incremented > 1 THEN
  8      :new.value_incremented := :new.value_incremented + 2;
  9    END IF;
 10  END;
 11  /

Trigger created.

SQL> SELECT * FROM incremented_values;

VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
             1                 2
             1                 2

SQL> INSERT INTO incremented_values VALUES(1,1);
Firing Trigger : increment_by_one
Firing Trigger : increment_by_two

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM incremented_values;

VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
             1                 2
             1                 2
             1	               4

SQL> SELECT referenced_name,referenced_type,dependency_type FROM user_dependencies WHERE name = 'INCREMENT_BY_TWO' AND referenced_type = 'TRIGGER';

REFERENCED_NAME                                                  REFERENCED_TYPE    DEPE
---------------------------------------------------------------- ------------------ ----
INCREMENT_BY_ONE                                                 TRIGGER            REF