Skip to content
Tutorials Center
My learning

Topics

Lesson 52 of 90 ยท Functions, PL/pgSQL and Triggers

Triggers

Learn PostgreSQL triggers: trigger functions, BEFORE and AFTER timing, row vs statement level, NEW and OLD, WHEN, transition tables, audit and updated_at.

A trigger runs code automatically when a table changes. You attach it to an event such as INSERT, UPDATE or DELETE, and from then on the database runs it every time that event happens, whichever application or person caused it. Triggers are the classic way to keep an updated_at column honest, to write an audit trail, or to enforce a rule that a CHECK constraint cannot express.

PostgreSQL splits a trigger into two parts. A trigger function holds the logic (written in PL/pgSQL or another language), and a trigger (CREATE TRIGGER) says when to call it. One function can serve many tables, which is what makes generic audit and timestamp triggers possible.

What runs live here. The live editor uses SQLite, which supports triggers with a different syntax: the body is a list of SQL statements, not a function. The live examples below show the same ideas (timing, NEW and OLD, WHEN, views) in SQLite's syntax. All PostgreSQL syntax, including trigger functions, statement-level triggers and transition tables, is shown read-only. For a complete SQLite-flavoured tour see Triggers in the SQL tutorial.

The two-step PostgreSQL syntax

First the trigger function. It takes no arguments in its signature and returns the special type trigger:

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

Then the trigger that wires it to a table:

SQL
CREATE TRIGGER articles_set_updated_at
BEFORE UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Whenever a row of articles is updated, PostgreSQL calls set_updated_at() first. Inside the function, NEW is the row about to be written; the function changes updated_at on it and returns it, and that modified row is what gets stored. Because the function never mentions articles, you can attach the same one to every table that has an updated_at column.

Timing: BEFORE, AFTER and INSTEAD OF

The timing decides what the trigger can do:

Timing Runs Typical use
BEFORE before the row is written change NEW, validate, or cancel the row
AFTER after the statement has written its rows audit logs, updating other tables, notifications
INSTEAD OF in place of the operation, on views only make a view writable

In a BEFORE ... FOR EACH ROW trigger, the return value controls what happens. Return NEW (possibly modified) to proceed with the change. Return NULL to skip this row silently, and the statement carries on with the other rows. To stop the whole statement with an error, call RAISE EXCEPTION. For BEFORE DELETE triggers, where there is no NEW, return OLD to let the delete proceed.

In an AFTER trigger the return value is ignored (return NULL by convention), because the row is already written. AFTER row triggers fire at the end of the statement, after all rows were processed, so by then they see the final state of the table.

Row-level and statement-level

FOR EACH ROW calls the function once for every affected row. FOR EACH STATEMENT (the default if you leave the clause out) calls it once per statement, even if the statement changed ten thousand rows, or none at all. Statement-level triggers have no NEW or OLD, but they can see what changed through transition tables (below).

SQLite has only row-level triggers. This live example shows the row-level behaviour: one trigger firing once per row, with a WHEN condition that skips unchanged values, the SQLite spelling of PostgreSQL's WHEN (OLD.price IS DISTINCT FROM NEW.price).

SQL Row triggers with WHEN

Press Run (or Ctrl + Enter) to see the result here.

Two log rows appear for the two products whose price really changed, and the stapler update produced nothing. The trigger ran three times for three affected rows, and WHEN stopped the third body from running. In PostgreSQL the WHEN clause is evaluated before the function is called, so skipped rows cost almost nothing, a big advantage over testing inside the function.

NEW, OLD and the special variables

Inside a PL/pgSQL trigger function PostgreSQL provides these variables:

  • NEW: the new row (INSERT and UPDATE). OLD: the old row (UPDATE and DELETE).
  • TG_OP: the operation as text: 'INSERT', 'UPDATE', 'DELETE' or 'TRUNCATE'.
  • TG_TABLE_NAME, TG_TABLE_SCHEMA: which table fired the trigger.
  • TG_WHEN, TG_LEVEL: 'BEFORE'/'AFTER' and 'ROW'/'STATEMENT'.
  • TG_ARGV[], TG_NARGS: arguments given in CREATE TRIGGER ... EXECUTE FUNCTION f('arg').

They let one function behave differently for each event:

SQL
CREATE FUNCTION audit_row()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, action, new_data)
    VALUES (TG_TABLE_NAME, 'I', to_jsonb(NEW));
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, 'U', to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;
  ELSE
    INSERT INTO audit_log (table_name, action, old_data)
    VALUES (TG_TABLE_NAME, 'D', to_jsonb(OLD));
    RETURN OLD;
  END IF;
END;
$$;

CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_row();

to_jsonb(NEW) turns the entire row into a JSON document, so one generic function records changes for any table without knowing its columns. The log rows are written in the same transaction as the change, so a rollback removes both, and an audit entry can never describe a change that did not happen.

Two related details. You can limit an update trigger to certain columns with AFTER UPDATE OF price, stock ON products, and a WHEN clause can compare OLD and NEW: WHEN (OLD.* IS DISTINCT FROM NEW.*) fires only when something truly changed.

Validation with BEFORE triggers

A CHECK constraint can only look at the row being written. A BEFORE trigger can look at other tables. Here is the SQLite version of "a workshop cannot be overbooked", using RAISE(ABORT, ...) to cancel the statement:

SQL A BEFORE trigger that refuses an insert

Press Run (or Ctrl + Enter) to see the result here.

Both seats are taken. A third INSERT (not run here, because the runner stops at the first error) would fail with workshop is full. The PostgreSQL version of the same rule has a trap: two sessions inserting at the same moment can both count one free seat and both succeed, because neither sees the other's uncommitted row. A real implementation must lock the workshops row first (SELECT ... FOR UPDATE) inside the trigger function. Concurrency rules like these are the subject of the locking lesson.

Transition tables

An AFTER trigger can ask PostgreSQL to expose the changed rows as read-only temporary tables with REFERENCING:

SQL
CREATE FUNCTION log_bulk_delete()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, old_data)
  SELECT TG_TABLE_NAME, 'D', to_jsonb(o) FROM old_rows AS o;
  RETURN NULL;
END;
$$;

CREATE TRIGGER orders_bulk_delete
AFTER DELETE ON orders
REFERENCING OLD TABLE AS old_rows
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_delete();

OLD TABLE holds all deleted rows and NEW TABLE holds all inserted or updated rows. One statement-level trigger with a transition table can replace tens of thousands of row-level calls with a single INSERT ... SELECT, which is far faster for bulk operations. A trigger with transition tables must handle one event type only (insert, update or delete).

Triggers on views

INSTEAD OF triggers make a view writable by translating the change. This works in SQLite as well:

SQL INSTEAD OF on a view

Press Run (or Ctrl + Enter) to see the result here.

The view update was rewritten into an update of the two real columns. PostgreSQL also has automatically updatable simple views, so INSTEAD OF triggers are only needed for views with joins, aggregates or computed columns.

Pick the trigger type

BEFORE row Fix or reject the row before it is written.
AFTER row Log or cascade once the row is stored.
AFTER statement One call per statement, with transition tables.
INSTEAD OF Make a complex view writable.
1 / 2

Order, recursion and pitfalls

  • Order. Several triggers on the same event run in alphabetical order by name. Name them so the order is obvious, or better, avoid depending on it.
  • Recursion. A trigger that updates its own table fires itself again. Guard with WHEN, with pg_trigger_depth() = 0, or by designing the logic to settle.
  • Bulk loads. Row triggers run once per row, which slows COPY and big updates. Statement triggers with transition tables are faster. You can disable a trigger during a load with ALTER TABLE ... DISABLE TRIGGER name, but remember to re-enable it.
  • Errors roll back everything. An exception in a trigger aborts the whole statement.
  • Surprises. Triggers hide work. Developers who read UPDATE orders SET ... do not see the extra writes. Name triggers clearly and list them with \d tablename in psql.

Managing triggers: list, disable, enable and drop

Once a trigger exists you will often need to see it, switch it off for a while, or remove it. The commands below were run on PostgreSQL 18 with a table emp that has a BEFORE trigger trg_touch and an AFTER INSERT trigger trg_log.

SQL
-- List the triggers of a table (psql's \d emp shows them too)
SELECT tgname, tgenabled FROM pg_trigger
WHERE tgrelid = 'emp'::regclass AND NOT tgisinternal ORDER BY tgname;
--  trg_log   | O      "O" = enabled
--  trg_touch | O

SELECT trigger_name, event_manipulation, action_timing
FROM information_schema.triggers WHERE event_object_table = 'emp' ORDER BY 1, 2;
--  trg_log   | INSERT | AFTER
--  trg_touch | INSERT | BEFORE
--  trg_touch | UPDATE | BEFORE        (a trigger on two events appears twice)

ALTER TABLE emp DISABLE TRIGGER trg_log;        -- skipped from now on; tgenabled becomes D
ALTER TABLE emp ENABLE TRIGGER trg_log;         -- back to O
ALTER TABLE emp DISABLE TRIGGER USER;           -- every trigger except constraint-backing ones
ALTER TABLE emp ENABLE TRIGGER ALL;
ALTER TABLE emp ENABLE ALWAYS TRIGGER trg_log;  -- fires even in replica mode (tgenabled A)

DROP TRIGGER trg_touch ON emp;                  -- the table name is required
DROP TRIGGER IF EXISTS trg_touch ON emp;        -- a notice instead of an error when it is missing
DROP TRIGGER trg_log ON emp CASCADE;            -- also drops objects that depend on the trigger

While trg_log was disabled, inserting an employee wrote no row into emp_log; after ENABLE, the next insert did. A disabled trigger is still defined, so \d emp lists it (marked disabled) and it comes back with the table in a dump. DISABLE TRIGGER USER also silences BEFORE triggers that fill columns like updated_at, so rows inserted in that state keep NULL there: re-enable as soon as the load is done.

Remember three details. Trigger names are unique per table, so DROP TRIGGER needs ON table. Dropping a trigger does not drop its function; run DROP FUNCTION touch(); afterwards if nothing else uses it. And DROP TRIGGER without IF EXISTS fails with trigger "trg_touch" for table "emp" does not exist if the name is wrong.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a BEFORE ... FOR EACH ROW trigger, what does returning NULL do?

    Returning NULL from a BEFORE row trigger cancels the operation for that row only, silently. To abort the whole statement, call RAISE EXCEPTION.
  2. 2. Which trigger timing can be used on a view?

    INSTEAD OF triggers replace the operation on a view, translating it into changes to the underlying tables. Row-level BEFORE and AFTER triggers apply to tables.
  3. 3. Why use a statement-level trigger with REFERENCING NEW TABLE for bulk loads?

    Row-level triggers call the function once per row. A statement-level trigger sees all rows at once through the transition table, so one set-based query replaces many calls.
  4. 4. Two triggers on the same table and event are defined. In what order does PostgreSQL run them?

    PostgreSQL fires triggers of the same kind in name order, so a naming prefix such as 10_ and 20_ gives you control.

Summary

  • A trigger is a trigger function (returns trigger) plus a CREATE TRIGGER that says when to call it, and one function can serve many tables.
  • BEFORE triggers can change or skip a row, AFTER triggers react to the stored change, and INSTEAD OF makes views writable.
  • Row-level triggers fire once per row, statement-level triggers once per statement, with NEW TABLE and OLD TABLE transition tables for bulk work.
  • NEW, OLD, TG_OP and TG_TABLE_NAME let one function handle any table; to_jsonb(NEW) is the key to generic audit logs.
  • WHEN skips the call entirely when it is not needed; triggers of one kind run in name order.
  • SQLite triggers (live) share the ideas but use a statement list instead of a function; the PL/pgSQL versions were read-only.

Next: Event Triggers, LISTEN and NOTIFY, where changes are announced to other programs.