Triggers

When, and how often

A trigger connects a table event to a function. Two choices define it:

INSTEAD OF triggers exist too, on views, to make non-updatable views accept writes.

Changing the row on its way in

The classic use: keep an updated_at column honest, whatever code does the update.

ALTER TABLE orders ADD COLUMN updated_at timestamptz;

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

CREATE TRIGGER orders_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

A row-level BEFORE trigger receives the new row as NEW. Whatever it returns is what gets written; returning NULL silently skips the row. For inserts, a column DEFAULT now() does the same job with no trigger at all.

Rejecting a change

A trigger that raises an error cancels the whole statement, not just the row:

CREATE OR REPLACE FUNCTION reject_big_orders() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.quantity > 10 THEN
    RAISE EXCEPTION 'quantity % is over the limit of 10', NEW.quantity;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER order_items_limit
BEFORE INSERT ON order_items
FOR EACH ROW EXECUTE FUNCTION reject_big_orders();

INSERT INTO order_items VALUES (106, 1, 2, 4500.00), (106, 2, 50, 1200.00);

ERROR:  quantity 50 is over the limit of 10
CONTEXT:  PL/pgSQL function reject_big_orders() line 4 at RAISE

The first row was valid, but the statement failed as a unit: order 106 still has exactly the one item it had before. For a rule about a single row like this one, a CHECK constraint is simpler and faster; triggers earn their place when a rule needs logic a constraint cannot express.

Recording what happened

An audit trail is the other classic use: every change also writes a copy to an audit table.

CREATE TABLE audit (table_name text, op text, row_data jsonb, at timestamptz DEFAULT now());

-- once per row
CREATE OR REPLACE FUNCTION audit_row() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit (table_name, op, row_data) VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
  RETURN NULL;
END $$;

-- once per statement, reading every new row from a transition table
CREATE OR REPLACE FUNCTION audit_statement() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit (table_name, op, row_data)
  SELECT TG_TABLE_NAME, TG_OP, to_jsonb(n) FROM new_rows n;
  RETURN NULL;
END $$;

CREATE TRIGGER order_items_audit AFTER INSERT ON order_items
REFERENCING NEW TABLE AS new_rows
FOR EACH STATEMENT EXECUTE FUNCTION audit_statement();

What triggers cost

Every bulk load, backfill and data fix pays that cost too, often without the person running it knowing the trigger exists.

Check yourself

1. A BEFORE INSERT row trigger returns NULL. What happens to the row?

   Show answer

   It is skipped silently: the insert does not happen for that row, and no error is raised.

   I could explain this without looking

2. A trigger rejects the second of two rows in one INSERT. Is the first row saved?

   Show answer

   No. The trigger’s error fails the whole statement, so neither row is inserted.

   I could explain this without looking

3. Why was the statement-level audit trigger faster than the row-level one?

   Show answer

   It ran once for the whole statement and inserted all audit rows from the transition table in one query, instead of calling the function and running a separate insert for each of the 200,000 rows.

   I could explain this without looking