PostgreSQL Triggers Tutorial with Examples
On this page
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:
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:
| Timing | Runs | Can change the row? | Typical use |
|---|---|---|---|
BEFORE | Before the row is written | Yes, or cancel it by returning NULL | Defaults, cleanup, validation |
AFTER | After the row is written | No | Audit logs, updating other tables |
INSTEAD OF | In place of the write, on views only | Not applicable | Making a complex view writable (Day 10) |
A BEFORE trigger is a good place to clean up input:
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.comUPDATE 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:
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:
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.00The 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 useDISABLE TRIGGER ALLcasually. 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.