Trigger execution order in PostgreSQL

Introduction

Triggers run automatically when certain events happen in the database. Knowing their order helps control what happens first and last.

You have multiple triggers on the same table and want to control which runs first.

You want to ensure data changes happen in a specific sequence.

You need to debug why some triggers seem to run before others.

You want to avoid conflicts between triggers that update the same data.

You want to organize complex business rules that depend on trigger order.

Syntax

PostgreSQL

CREATE TRIGGER trigger_name
  { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
  ON table_name
  [ FROM referenced_table ]
  [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]
  [ FOR [ EACH ] { ROW | STATEMENT } ]
  [ WHEN ( condition ) ]
  EXECUTE FUNCTION function_name ( arguments );

PostgreSQL does not guarantee the order of triggers firing if multiple triggers have the same timing and event.

To control order, you can name triggers with a prefix or suffix and drop/recreate them in the desired order.

Examples

A trigger that runs before inserting a row into the employees table.

PostgreSQL

CREATE TRIGGER trg_before_insert_1
  BEFORE INSERT ON employees
  FOR EACH ROW
  EXECUTE FUNCTION log_insert();

A trigger that runs after inserting a row into the employees table.

PostgreSQL

CREATE TRIGGER trg_after_insert_2
  AFTER INSERT ON employees
  FOR EACH ROW
  EXECUTE FUNCTION update_stats();

Sample Program

This example creates two BEFORE INSERT triggers on the same table. When inserting, both triggers run and print notices. The order of notices shows trigger execution order.

PostgreSQL

CREATE TABLE test_order (
  id SERIAL PRIMARY KEY,
  info TEXT
);

CREATE OR REPLACE FUNCTION trg_func_1() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Trigger 1 executed';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trg_func_2() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Trigger 2 executed';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger1
  BEFORE INSERT ON test_order
  FOR EACH ROW
  EXECUTE FUNCTION trg_func_1();

CREATE TRIGGER trigger2
  BEFORE INSERT ON test_order
  FOR EACH ROW
  EXECUTE FUNCTION trg_func_2();

INSERT INTO test_order (info) VALUES ('test');

Important Notes

PostgreSQL runs triggers in alphabetical order by trigger name when they have the same timing and event.

You can control execution order by naming triggers with prefixes like 'a_', 'b_', etc.

Use RAISE NOTICE in trigger functions to debug and see the order triggers run.

Summary

Triggers run automatically on table events like INSERT, UPDATE, DELETE.

When multiple triggers exist for the same event and timing, PostgreSQL runs them in alphabetical order by trigger name.

Naming triggers carefully helps control their execution order.