Oracle PL/SQL Best Practices

Name

MOD-11: Consolidate “overlapping” DML triggers to control execution order.

Synopsis

While it’s possible to create many DML triggers of the same type on a table, it isn’t possible to guarantee the order in which they fire. While several theories abound about firing order (including reverse order of creation or object ID), it isn’t advisable to rely on theories when designing database triggers. Instead, you should consolidate into a single trigger all triggers that fire under the same conditions.

Example

When inserting a value of 1 for the following ID field, what value will wind up in the table?

CREATE OR REPLACE TRIGGER increment_by_one
BEFORE INSERT ON id_table
FOR EACH ROW
BEGIN
  :new.id := :new.id + 1;
END;
/

CREATE OR REPLACE TRIGGER increment_by_two
BEFORE INSERT ON id_table
FOR EACH ROW
BEGIN
  IF :new.id > 1 THEN
    :new.id := :new.id + 2;
  END IF;
END;
/

The answer is, in reality, indeterminate; you can’t accurately predict the behavior of such a system of triggers.

Benefits

You don’t have to be concerned about the order in which triggers fire when the application is rebuilt, moved, or upgraded.

Challenges

It may be difficult to move complex code into a single trigger.

You may also have some trouble identifying triggers that fire under the same conditions. See Section for a query you can run that should help answer this question.

Resources

1. multiple_triggers.sql : Contains a detailed working version of the example.

2. trigger_conflict.sql : A simple query against the USR_TRIGGERS data ...