Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 3 · Lesson 16 · 7 min read

PostgreSQL Triggers Tutorial with Examples

On this page
  1. Why use a trigger
  2. Step 1: An updated_at trigger
  3. Step 2: BEFORE, AFTER and INSTEAD OF triggers
  4. Step 3: An audit log trigger
  5. Step 4: Trigger performance and pitfalls
  6. Check yourself

Why use a trigger

Most tables get an updated_at column. It's set on insert, and then every code path that updates the row is supposed to set it too. One script, one admin tool or one forgotten ORM call later, and the column can't be trusted.

You can't fix that by being more careful in application code, because not every write goes through your application. A trigger fixes it in the database. It's a function PostgreSQL runs automatically whenever a row is inserted, updated or deleted, no matter who made the change.

Step 1: An updated_at trigger

A trigger has two parts. First a function that returns the special type trigger. Then a CREATE TRIGGER statement that attaches it to a table:

SQL
CREATE TABLE products (
  sku         text PRIMARY KEY,
  name        text NOT NULL,
  list_price  numeric(10,2) NOT NULL,
  updated_at  timestamptz NOT NULL DEFAULT now()
);

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

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

Inside the function, NEW is the row about to be written. Change it, return it, and that's what gets stored. Now updated_at is correct whoever runs the UPDATE.

One trap: a BEFORE row trigger must return the row. If it returns NULL, PostgreSQL silently skips the write for that row, with no error. That's sometimes deliberate, but it's a nasty surprise when it isn't.

Step 2: BEFORE, AFTER and INSTEAD OF triggers

When a trigger runs decides what it can do:

TimingRunsCan change the row?Typical use
BEFOREBefore the row is writtenYes, or cancel it by returning NULLDefaults, cleanup, validation
AFTERAfter the row is writtenNoAudit logs, updating other tables
INSTEAD OFIn place of the write, on views onlyNot applicableMaking a complex view writable (Day 10)

A BEFORE trigger is a good place to clean up input:

SQL
CREATE TABLE customers (
  customer_id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email        text NOT NULL UNIQUE
);

CREATE FUNCTION normalize_email() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));
  RETURN NEW;
END;
$$;

CREATE TRIGGER customers_normalize_email
BEFORE INSERT OR UPDATE OF email ON customers
FOR EACH ROW EXECUTE FUNCTION normalize_email();

INSERT INTO customers (email) VALUES ('  Ana@Example.COM ');
SELECT email FROM customers;   -- ana@example.com

UPDATE OF email means the trigger only fires for updates that touch that column. For simple rules, though, prefer a CHECK constraint (Day 5). It's faster and easier to see.

An INSTEAD OF trigger makes a join view writable, which Day 10 promised. This view combines two tables, so PostgreSQL can't route an INSERT on its own. The trigger says where each part goes:

SQL
CREATE TABLE customer_profiles (
  customer_id  int PRIMARY KEY REFERENCES customers,
  full_name    text NOT NULL
);
CREATE VIEW customer_cards AS
SELECT c.customer_id, c.email, p.full_name
FROM customers c JOIN customer_profiles p USING (customer_id);

CREATE FUNCTION customer_cards_insert() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO customers (email) VALUES (NEW.email)
  RETURNING customer_id INTO NEW.customer_id;
  INSERT INTO customer_profiles (customer_id, full_name) VALUES (NEW.customer_id, NEW.full_name);
  RETURN NEW;
END;
$$;

CREATE TRIGGER customer_cards_insert
INSTEAD OF INSERT ON customer_cards
FOR EACH ROW EXECUTE FUNCTION customer_cards_insert();

INSERT INTO customer_cards (email, full_name) VALUES ('ben@example.com', 'Ben Ortiz')
RETURNING customer_id;

The insert creates one row in each table. Setting NEW.customer_id before RETURN NEW is what lets RETURNING show the generated id. UPDATE and DELETE on the view need their own INSTEAD OF triggers.

Step 3: An audit log trigger

Here's the most common real-world trigger. One generic AFTER trigger writes the before and after versions of each row to an audit table as JSONB:

SQL
CREATE TABLE audit_log (
  audit_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name  text NOT NULL,
  operation   text NOT NULL,
  changed_by  text NOT NULL DEFAULT current_user,
  changed_at  timestamptz NOT NULL DEFAULT now(),
  old_data    jsonb,
  new_data    jsonb
);

CREATE FUNCTION audit_row_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES (TG_TABLE_NAME, TG_OP,
          CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
  RETURN NULL;   -- ignored for AFTER triggers
END;
$$;

CREATE TRIGGER products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION audit_row_change();

INSERT INTO products (sku, name, list_price) VALUES ('SKU-1', 'Mug', 12);
UPDATE products SET list_price = 14 WHERE sku = 'SKU-1';
SELECT operation, old_data ->> 'list_price' AS old, new_data ->> 'list_price' AS new
FROM audit_log;
-- INSERT | NULL  | 12.00
-- UPDATE | 12.00 | 14.00

The trigger function gets some useful variables for free. OLD is the row before the change (UPDATE and DELETE), NEW is the row after (INSERT and UPDATE), TG_OP is the operation name and TG_TABLE_NAME is the table. Attach the same function to any table you want audited.

The trigger runs inside the same transaction as the change. If the change rolls back, so does its audit row. The log only ever shows changes that really committed.

Step 4: Trigger performance and pitfalls

Triggers are invisible. Someone reading UPDATE products ... in the application has no idea three functions just ran. Keep that in mind, and watch for these costs:

  • Row-level triggers run once per row. An UPDATE of a million rows runs your function a million times. If you only need one reaction per statement, such as clearing a cache, use FOR EACH STATEMENT. It can still see all changed rows through a transition table (REFERENCING NEW TABLE AS new_rows).
  • Use WHEN to skip work. WHEN (OLD.list_price IS DISTINCT FROM NEW.list_price) on CREATE TRIGGER stops the function from even being called when the price didn't change.
  • Several triggers on one table fire in alphabetical order of their names. If order matters, name them to make it obvious.
  • Turning triggers off for a bulk load is possible with ALTER TABLE products DISABLE TRIGGER products_audit. Never use DISABLE TRIGGER ALL casually. PostgreSQL enforces foreign keys with internal triggers, and turning those off lets orphaned rows in.

For changes to the schema itself, rather than the data, PostgreSQL has event triggers. They fire on DDL commands like CREATE, ALTER and DROP across the whole database, for example to log schema changes or block DROP TABLE in production. Only a superuser can create them, and a buggy one can block your own migrations, so test them carefully.

Check yourself

The trigger function returns NULL, which tells PostgreSQL to skip the row. A BEFORE row trigger must RETURN NEW for the insert to go ahead.

Add a WHEN clause so the trigger only fires on rows whose price changed, or switch to a statement-level trigger that logs the batch from a transition table. For a one-off load, you could disable that one trigger, not ALL, and record the change another way.

The trigger catches every change, including manual fixes, scripts and other services that never call your application. It also commits or rolls back together with the change it records.

A row-level trigger (FOR EACH ROW) runs once per affected row and can see or change NEW and OLD. A statement-level trigger runs once per statement, however many rows it touched. For bulk audit logging, a statement-level trigger with transition tables (REFERENCING NEW TABLE AS ...) is far cheaper than one call per row.

The trigger runs inside the transaction, so a slow or failing service makes every write slow or fail, and if the transaction later rolls back, the external call can't be undone. Write the event to an outbox table in the same transaction and let a separate worker send it.