Triggers

A trigger is a function that PostgreSQL calls automatically when a specified event (INSERT, UPDATE, DELETE, or TRUNCATE) occurs on a table. Triggers are written in PL/pgSQL and are one of the most powerful features of PostgreSQL.

How Triggers Work

1. You write a trigger function that returns TRIGGER
2. You create a trigger that binds the function to a table and event

Writing a Trigger Function

CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
  -- use NEW and OLD records
  RETURN NEW;  -- or RETURN OLD, or RETURN NULL
END;
$$;

Trigger functions take no parameters and return TRIGGER.

Special Variables in Trigger Functions

Variable	Available on	Description
NEW	INSERT, UPDATE	The new row being inserted or updated
OLD	UPDATE, DELETE	The old row before the change
TG_OP	All	Operation: 'INSERT', 'UPDATE', 'DELETE'
TG_TABLE_NAME	All	Name of the table that fired the trigger
TG_WHEN	All	'BEFORE' or 'AFTER'

Creating a Trigger

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF }
{ INSERT | UPDATE | DELETE } [ OR ... ]
ON table_name
[ FOR EACH ROW | FOR EACH STATEMENT ]
EXECUTE FUNCTION trigger_function_name();

Example: Updated-At Timestamp

A common use case — automatically update a updated_at column:

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

CREATE TRIGGER trg_employees_updated_at
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Now every UPDATE on employees automatically sets updated_at.

Example: Audit Log

CREATE OR REPLACE FUNCTION audit_employee_changes()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO employee_audit(employee_id, action, changed_at)
    VALUES (OLD.id, 'DELETE', NOW());
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO employee_audit(employee_id, action, changed_at)
    VALUES (NEW.id, 'UPDATE', NOW());
    RETURN NEW;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_employee_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION audit_employee_changes();

BEFORE vs AFTER Triggers

Type	Runs	Can modify NEW?
BEFORE	Before the operation	Yes — return the modified NEW
AFTER	After the operation	No — data already written