Triggers

A trigger is procedural code that PostgreSQL automatically runs in response to a data-changing event — INSERT, UPDATE, or DELETE — on a table, without the application having to explicitly call anything.

Timing and granularity

Two independent choices control exactly when and how often a trigger fires:

Dimension	Options	Meaning
Timing	`BEFORE` / `AFTER` / `INSTEAD OF`	`BEFORE` runs before the change is applied (can inspect/modify the row, or cancel it); `AFTER` runs once the change is already applied; `INSTEAD OF` replaces the operation entirely and is used on views
Granularity	`FOR EACH ROW` / `FOR EACH STATEMENT`	Row-level fires once per affected row; statement-level fires once total for the whole statement, regardless of how many rows it touched

PostgreSQL's two-part trigger system

Unlike some databases where a trigger's logic is written inline, PostgreSQL splits a trigger into two separate pieces: a CREATE TRIGGER statement that declares when and on what the trigger fires, and a separately-defined trigger function — written in PL/pgSQL — that contains the actual logic and must declare its return type as the special TRIGGER pseudo-type.

Note

This two-piece structure is distinctly PostgreSQL's approach. The trigger function is a normal, reusable database object; the same trigger function can be attached to several different triggers (even on different tables) via separate CREATE TRIGGER statements.

Worked example: auto-updating an updated_at column

A very common pattern: keep an updated_at timestamp column current automatically, without every application code path having to remember to set it.

1. The trigger function

SQL

CREATE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

2. The trigger that calls it

SQL

CREATE TRIGGER trg_products_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Now every update to products automatically stamps updated_at, regardless of which application code path performed the update:

SQL

UPDATE products SET price = 24.99 WHERE id = 12;
-- updated_at is set to the current time automatically, without being
-- mentioned anywhere in this statement

Another classic use case: auditing

SQL

CREATE FUNCTION audit_products_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO products_audit (product_id, changed_at, old_price, new_price)
  VALUES (OLD.id, now(), OLD.price, NEW.price);
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_audit
AFTER UPDATE ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION audit_products_change();

This records a row in products_audit every time a product's price actually changes, purely as a side effect of the UPDATE statement — no application code needs to know the audit table exists.

Warning

Triggers make behavior harder to trace: a plain UPDATE products SET price = ... can silently cascade into writes on other tables, extra validation, or even cancellation of the update, none of which is visible at the call site. Use triggers judiciously — for cross-cutting concerns like timestamps and audit trails they're a great fit, but piling up business logic in triggers can make a codebase very difficult to reason about.

Triggers run automatically on INSERT/UPDATE/DELETE, with BEFORE, AFTER, or INSTEAD OF timing.
Row-level (FOR EACH ROW) fires per affected row; statement-level (FOR EACH STATEMENT) fires once per statement.
PostgreSQL splits a trigger into a CREATE TRIGGER declaration and a separate PL/pgSQL trigger function returning TRIGGER.
Great for cross-cutting concerns like updated_at timestamps and audit logs; risky as a home for core business logic.