Postgres by Example: Triggers

A trigger is a function that PostgreSQL calls automatically when a specific event happens on a table — INSERT, UPDATE, DELETE, or TRUNCATE, fired BEFORE or AFTER the row change, for each row or for the whole statement. Triggers are the standard tool for enforcing invariants the type system cannot, auditing changes, denormalizing for performance, and keeping updated_at timestamps current.

What you'll learn:

• The BEFORE vs AFTER distinction
• Row-level (FOR EACH ROW) vs statement-level (FOR EACH STATEMENT)
• The NEW and OLD row variables
• A canonical example: auto-updating updated_at
• An audit-log trigger that writes to a side table

-- Setup
DROP TABLE IF EXISTS articles_example;
DROP TABLE IF EXISTS articles_audit;
CREATE TABLE articles_example (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT current_timestamp,
  updated_at  timestamptz NOT NULL DEFAULT current_timestamp
);

CREATE TABLE articles_audit (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  article_id  bigint NOT NULL,
  action      text   NOT NULL,
  by_user     text   NOT NULL DEFAULT current_user,
  at          timestamptz NOT NULL DEFAULT current_timestamp,
  old_row     jsonb,
  new_row     jsonb
);

-- Trigger function: bump updated_at before every UPDATE
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := current_timestamp;
  RETURN NEW;
END;
$$;

CREATE TRIGGER articles_updated_at
BEFORE UPDATE ON articles_example
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- Trigger function: write to the audit log after every change
CREATE OR REPLACE FUNCTION audit_article_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO articles_audit (article_id, action, old_row, new_row)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN NULL;  -- AFTER trigger return value is ignored
END;
$$;

CREATE TRIGGER articles_audit
AFTER INSERT OR UPDATE OR DELETE ON articles_example
FOR EACH ROW
EXECUTE FUNCTION audit_article_change();

-- Exercise the triggers
INSERT INTO articles_example (title, body) VALUES ('Hello', 'world');
UPDATE articles_example SET body = 'edited world' WHERE title = 'Hello';
DELETE FROM articles_example;

SELECT * FROM articles_audit ORDER BY id;

-- Cleanup
DROP TRIGGER IF EXISTS articles_updated_at ON articles_example;
DROP TRIGGER IF EXISTS articles_audit ON articles_example;
DROP FUNCTION IF EXISTS set_updated_at();
DROP FUNCTION IF EXISTS audit_article_change();
DROP TABLE IF EXISTS articles_example;
DROP TABLE IF EXISTS articles_audit;

A trigger is two pieces: a trigger function (returns trigger) and a trigger that ties the function to a table and an event. Inside the function:

• NEW is the new row (for INSERT and UPDATE).
• OLD is the old row (for UPDATE and DELETE).
• TG_OP is the operation ('INSERT', 'UPDATE', 'DELETE', 'TRUNCATE').
• TG_TABLE_NAME etc. are also available for reusable triggers.

For BEFORE ROW triggers, returning NEW lets the row through (possibly modified); returning NULL cancels the operation. For AFTER ROW triggers, the return value is ignored — the operation already happened. Statement-level triggers (FOR EACH STATEMENT) fire once per statement regardless of row count and do not have NEW/OLD.

The updated_at bumper is the most common trigger in the world. Define one trigger function, attach it to every table with an updated_at column.

To run:

$ psql -f source/triggers.sql postgres
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 1
UPDATE 1
DELETE 1
 id | article_id | action  | by_user  |              at               | old_row | new_row
----+------------+---------+----------+-------------------------------+---------+---------
  1 |          1 | INSERT  | your_user | 2026-xx-xx ...                |         | {"id":1,...}
  2 |          1 | UPDATE  | your_user | 2026-xx-xx ...                | {...}   | {...}
  3 |          1 | DELETE  | your_user | 2026-xx-xx ...                | {...}   |
(3 rows)
 ...

Common pitfalls:

• A BEFORE trigger that returns NULL silently swallows the row — the INSERT/UPDATE is skipped without an error. Make sure you return NEW unless you mean to cancel.
• Triggers fire inside the same transaction as the triggering statement. Heavy logic in a trigger slows every modification of the table.
• Triggers do not fire on COPY ... FROM by default? They do, actually — but TRUNCATE does not fire row-level triggers (only statement-level). Plan accordingly.
• Putting business logic in triggers makes it hard to discover from application code. Many teams limit triggers to infrastructure concerns (updated_at, audit) and keep business logic in the application.

Tip: Use pg_trigger_depth() inside a trigger to detect recursion. A trigger that updates the same table that fired it can loop — guard with IF pg_trigger_depth() > 1 THEN RETURN NEW; END IF;.

Try it: Add an updated_at column to fruits and attach the trigger from above. Update a row and confirm updated_at changes. Then add a BEFORE INSERT trigger that uppercases name automatically, and see it in action.

Source: triggers.sql

Next: Full-Text Search

Home: Postgres by Example