PostgreSQL triggers
A trigger runs a function automatically when rows are inserted, updated or deleted. Typical uses: maintaining an updated_at column, writing an audit log, enforcing a rule that a CHECK can’t express, or keeping a summary table in sync.
The trigger function must RETURNS trigger. Inside it, NEW is the row being written and OLD the previous version; TG_OP tells you the operation. A BEFORE row trigger can change NEW before it is saved (or return NULL to skip the row); an AFTER trigger sees the final result.
Use triggers with restraint: logic that fires invisibly can surprise people later. Document them.
Syntax
CREATE TRIGGER name {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table FOR EACH ROW EXECUTE FUNCTION trigger_fn();
Examples
Edit the code and run it. Output is printed the way psql prints it.
25.1Automatic updated_at
ALTER TABLE products ADD COLUMN IF NOT EXISTS updated_at timestamptz;
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS products_updated_at ON products;
CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
UPDATE products SET stock = stock - 1 WHERE id = 1;
SELECT id, name, stock, updated_at FROM products WHERE id IN (1, 2);The BEFORE UPDATE trigger edits NEW just before the row is saved. Product 1 gets a timestamp; product 2, untouched, stays NULL.
25.2An audit log
DROP TABLE IF EXISTS price_audit;
CREATE TABLE price_audit (
product_id integer,
old_price numeric(10,2),
new_price numeric(10,2),
changed_by text DEFAULT current_user,
changed_at timestamptz DEFAULT now()
);
CREATE OR REPLACE FUNCTION log_price_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
IF NEW.price IS DISTINCT FROM OLD.price THEN
INSERT INTO price_audit (product_id, old_price, new_price)
VALUES (OLD.id, OLD.price, NEW.price);
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS products_price_audit ON products;
CREATE TRIGGER products_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW EXECUTE FUNCTION log_price_change();
UPDATE products SET price = price - 5 WHERE category = 'Audio';
SELECT * FROM price_audit;AFTER UPDATE OF price fires only when the price column is in the UPDATE. Comparing OLD and NEW logs real changes only.
25.3A trigger that blocks bad data
CREATE OR REPLACE FUNCTION prevent_overselling() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
v_stock integer;
BEGIN
SELECT stock INTO v_stock FROM products WHERE id = NEW.product_id;
IF NEW.quantity > v_stock THEN
RAISE EXCEPTION 'Only % in stock for product %', v_stock, NEW.product_id;
END IF;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS check_stock ON order_items;
CREATE TRIGGER check_stock
BEFORE INSERT ON order_items
FOR EACH ROW EXECUTE FUNCTION prevent_overselling();
-- product 7 has 0 in stock:
INSERT INTO order_items VALUES (19, 7, 1, 59.00);The insert fails with your own message. A CHECK constraint can’t do this because it can only look at the row itself, not at another table.