Triggers Basics

9 examples to get you started with PostgreSQL triggers - 6 basic and 3 intermediate.

Prerequisites

Table with INSERT/UPDATE you can modify in dev.

# Local dev with Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Basic Examples

1. AFTER INSERT Row Trigger

Runs per inserted row.

CREATE OR REPLACE FUNCTION app.log_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE 'inserted %', NEW.id;
  RETURN NEW;
END;
$$;
CREATE TRIGGER tr_log_insert AFTER INSERT ON app.accounts
FOR EACH ROW EXECUTE FUNCTION app.log_insert();

• RETURN NEW in row triggers
• AFTER triggers cannot change row
• Use NOTICE only in dev

2. BEFORE UPDATE Row Trigger

Can modify NEW before write.

CREATE OR REPLACE FUNCTION app.touch_updated() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

• BEFORE UPDATE sets NEW columns
• Common for updated_at
• Prefer GENERATED or app layer if simple

3. Statement-Level Trigger

Fires once per SQL statement.

CREATE TRIGGER tr_stmt AFTER UPDATE ON app.accounts
FOR EACH STATEMENT EXECUTE FUNCTION app.log_insert();

• No NEW/OLD row variables
• Use transition tables for bulk audit
• Cheaper per row on mass UPDATE

4. DROP Trigger

Remove when logic moves to app.

DROP TRIGGER IF EXISTS tr_log_insert ON app.accounts;

• Migrations should DROP before CREATE
• Orphan triggers confuse debugging
• Document ownership

5. Trigger Timing

BEFORE vs AFTER choice.

-- BEFORE: validate/modify NEW
-- AFTER: audit/side effects referencing final row

• BEFORE for validation and defaults
• AFTER for audit and fan-out
• INSTEAD OF on views only

6. WHEN Clause

Conditional trigger fire.

CREATE TRIGGER tr_status AFTER UPDATE OF status ON app.orders
FOR EACH ROW WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION app.log_insert();

• Reduces noise on wide UPDATE
• Cannot reference other tables
• Test with bulk updates

Intermediate Examples

7. Multiple Triggers Order

Alphabetical name order by default.

CREATE TRIGGER a_first BEFORE INSERT ON app.accounts FOR EACH ROW EXECUTE FUNCTION app.touch_updated();
CREATE TRIGGER b_second BEFORE INSERT ON app.accounts FOR EACH ROW EXECUTE FUNCTION app.log_insert();

• Name triggers for intended order
• Avoid conflicting BEFORE modifiers
• Consolidate tiny triggers when possible

8. Constraint Trigger

Deferred constraint checking.

-- CONSTRAINT TRIGGER ... DEFERRABLE INITIALLY DEFERRED

• Rare; FK and CHECK usually enough
• Runs at commit end
• Harder to debug

9. Disable Trigger Session

Session_replication_role or ALTER TABLE DISABLE.

ALTER TABLE app.accounts DISABLE TRIGGER tr_log_insert;
-- bulk load
ALTER TABLE app.accounts ENABLE TRIGGER tr_log_insert;

• Superuser or owner required
• Document maintenance windows
• Re-enable before returning to app traffic

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.