skip to content

A table has three separate AFTER INSERT row-level triggers on it, written by three different teams. What determines the order in which they execute, and why do experienced engineers avoid writing triggers whose correctness depends on that order?

level: middleimportance: should knowfreq 42%

answer

  1. same event, same timing = engine picks
  2. Postgres: alphabetical trigger name
  3. Oracle/MySQL: FOLLOWS / PRECEDES
  4. SQL Server: only first + last pinned
  5. rename or re-create silently reshuffles

basics

~20 s

Order is engine-defined, not portable: some engines order by trigger name, some by creation time, some let you pin only a first and last trigger. Renames, re-creations and dump restores silently change it, so keep triggers order-independent.

solid answer

~50 s

Nothing reliable guarantees the order. The old SQL standard said creation order; engines diverge. PostgreSQL fires same-timing triggers in alphabetical name order. Oracle left it unspecified until it added FOLLOWS/PRECEDES for a declared partial order. SQL Server lets you pin only one first and one last trigger per action, with the middle unordered. MySQL allowed one trigger per timing for years, then added FOLLOWS/PRECEDES. The order is also easy to disturb without touching logic: renaming a trigger, dropping and re-creating one in a migration, or restoring a logical dump can reshuffle it. Order starts to matter as soon as triggers stop being commutative: a BEFORE trigger that rewrites the new row changes what the next BEFORE trigger sees, and an AFTER trigger that reads the table sees writes made by AFTER triggers that already ran. So either make each trigger independent of the others, or collapse them into one trigger per table per timing whose body calls procedures in an explicit, readable sequence.

code

sql · 10 lines
sql
CREATE TRIGGER a_normalize_email
  BEFORE INSERT ON customer
  FOR EACH ROW EXECUTE FUNCTION normalize_email();

CREATE TRIGGER b_validate_email
  BEFORE INSERT ON customer
  FOR EACH ROW EXECUTE FUNCTION validate_email();

-- Renaming a_normalize_email to z_normalize_email reverses the pair
-- and validation then runs on the un-normalized value.

go deeper

for a junior

Know that a table can have more than one trigger for the same event and that the database, not you, decides the order.

for a middle

Name at least one concrete engine rule (alphabetical name order, or first/last pinning) and explain that renames and re-creations can change it.

for a senior

Frame it as hidden coupling: the dependency is not expressed in either trigger's source, so consolidate into one trigger calling procedures in explicit order.

for a principal

Argue the governance angle — cap triggers per table, treat trigger order as a reviewed contract, and prefer moving multi-step logic out of the trigger layer entirely.

## The collision A trigger is code the database runs automatically when a DML statement touches a table. Nothing stops several triggers from being defined on the same table for the same event and the same timing — three AFTER INSERT ... FOR EACH ROW triggers, say. They are independent schema objects, usually created at different times by different people: one stamps audit columns, one maintains a denormalized counter, one writes a row into a queue table. The engine must pick an execution order. ## The standard is weak and engines disagree SQL:1999 said triggers for the same event fire in order of creation timestamp. Real engines took different routes: - **PostgreSQL** fires triggers of the same timing in alphabetical order of trigger name. All BEFORE row triggers run as a group, then the row is written, then all AFTER row triggers. - **Oracle** historically documented the order as unspecified; later versions added FOLLOWS and PRECEDES so one trigger can be declared to run after or before another named trigger. - **SQL Server** exposes a mechanism to mark at most one trigger as first and one as last for a given action; everything else is unordered. Only one INSTEAD OF trigger per action is allowed at all. - **MySQL** permitted only one trigger per timing+event for years; newer versions allow several plus FOLLOWS/PRECEDES. So there is no portable rule, and even the engine-specific rule is fragile. Alphabetical order changes when someone renames a trigger. Creation order changes when a migration drops and re-creates one trigger, or when a logical dump is restored and objects come back in a different sequence than production had. None of those actions look like behavior changes in review. ## When order actually becomes load-bearing If every trigger touches disjoint state and none reads what another writes, order is irrelevant and you are safe by construction. Coupling creeps in three ways: 1. **BEFORE triggers mutate the row.** A BEFORE row trigger may modify the new row before it is stored. The next BEFORE trigger sees the mutated version, so a normalizing trigger and a validating trigger produce different results depending on which runs first. 2. **AFTER triggers read the table.** An AFTER trigger that recomputes a total sees rows written by AFTER triggers that already fired. 3. **One trigger aborts.** If a trigger raises an error, later triggers for that event never run, so "the audit row is always written" quietly becomes "unless the validation trigger fires first". These dependencies are invisible: the coupling is not expressed anywhere in either trigger's source. ## How to design it away - Prefer **one trigger per table per timing**. Its body calls stored procedures in an explicit order you can read in a single file, and the order lives in version control rather than in the catalog. - Keep trigger effects **commutative and idempotent** where you can, so ordering cannot change the outcome. - If you genuinely need an order, use the engine's explicit mechanism (FOLLOWS/PRECEDES or first/last designation), and add a test that fails when the order breaks. - Treat trigger names as part of the contract in engines that order by name; document why a name starts with a numeric prefix rather than letting the next person "tidy" it. - Inventory triggers in code review the way you inventory indexes: a table with more than one or two is a design smell. ## The honest interview point The strong answer is not reciting each vendor's rule. It is: the order is an implementation detail that changes under renames and redeploys, so correctness must not depend on it — and if it must, the dependency has to be declared, not assumed.

  • How would you collapse three AFTER INSERT triggers into one without losing team ownership of each piece of logic?
    Keep each team's logic in its own stored procedure or function, and have a single trigger body call them in a fixed sequence. Ownership stays per-procedure, but the order becomes explicit source in one place, reviewable and diffable. It also gives you one place to add error handling and one object to disable during a bulk load.
  • Does the ordering problem apply across the BEFORE and AFTER phases too?
    No — the phases themselves are well defined and portable: all BEFORE triggers for a row run before the row change is applied, and all AFTER triggers run after it. The unspecified part is only the relative order of triggers within the same phase and event. Statement-level triggers similarly bracket the whole statement.

saying these in an interview costs you the question

  • Claiming triggers always fire in creation order because the SQL standard says so
  • Assuming trigger order is the same across PostgreSQL, Oracle, MySQL and SQL Server
  • Encoding a required order by 'just naming them carefully' with no comment or test
  • Believing a failing trigger still lets the remaining triggers for that event run
  • Thinking multiple INSTEAD OF triggers can be layered on the same action

context

resolving links…