Lesson 27: Triggers

A trigger automatically runs logic in response to a table event (INSERT/UPDATE/DELETE) — useful for audit logs and enforcing side-effects the application can't be trusted to always do.

Real-time example

Every time an order's status changes, an audit_log row is written automatically, even if the change came from a raw SQL script.

CREATE TABLE audit_log (id SERIAL, order_id INT, old_status TEXT, new_status TEXT, changed_at TIMESTAMP DEFAULT now());

CREATE OR REPLACE FUNCTION log_status_change() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log(order_id, old_status, new_status)
    VALUES (OLD.id, OLD.status, NEW.status);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_status
AFTER UPDATE OF status ON orders
FOR EACH ROW EXECUTE FUNCTION log_status_change();

What's happening

OLD and NEW refer to the row's values before and after the update — triggers see both, which is exactly what audit logging needs.

Triggers are powerful but invisible in application code — document them clearly, or future developers will be confused by 'magic' rows appearing.