Triggers & Audit Logs

A trigger runs a function automatically when rows change. BEFORE triggers can modify the row before it is saved (such as setting updated_at); AFTER triggers react to the saved change (such as writing an audit record).

Inside a trigger function, NEW is the incoming row and OLD the previous one; TG_OP tells you whether it was an INSERT, UPDATE or DELETE. to_jsonb(row) captures a whole row for auditing.

Use triggers for rules that must hold no matter which application changes the data. Keep them small and fast — heavy logic in triggers makes every write slower and harder to debug.

triggers.sqlSQL

-- 1. Keep updated_at current automatically
ALTER TABLE students ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

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

CREATE TRIGGER students_set_updated_at
BEFORE UPDATE ON students
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- 2. Audit every change to results
CREATE TABLE audit_log (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name text        NOT NULL,
    operation  text        NOT NULL,
    old_row    jsonb,
    new_row    jsonb,
    changed_by text        NOT NULL DEFAULT current_user,
    changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_changes() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, old_row, new_row)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
    );
    RETURN NULL;   -- return value is ignored for AFTER triggers
END;
$$;

CREATE TRIGGER results_audit
AFTER INSERT OR UPDATE OR DELETE ON results
FOR EACH ROW EXECUTE FUNCTION audit_changes();

UPDATE results SET score = 45 WHERE student_id = 4 AND subject_id = 1 AND term = 2;

SELECT operation,
       old_row ->> 'score' AS old_score,
       new_row ->> 'score' AS new_score,
       changed_by
FROM audit_log;

Runs in your browser · PostgreSQL

Key points

BEFORE triggers adjust the row; AFTER triggers react to the saved change.
NEW, OLD and TG_OP describe the change inside the trigger function.
Audit triggers record every change regardless of which app made it.

Exercise

Add a trigger on payments that automatically reduces the student's fee_balance when a payment is inserted. Test it, and think about what should happen if a payment is deleted.

Show solution

Try the exercise yourself first — then compare your approach with this one.

One AFTER trigger handles both cases: a new payment reduces the student's balance, and deleting a payment (for example, one recorded by mistake) adds the amount back. Because it runs inside the same transaction, the payment and the balance can never get out of step.

payment-trigger.sqlSQL

CREATE OR REPLACE FUNCTION apply_payment() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE students SET fee_balance = fee_balance - NEW.amount WHERE id = NEW.student_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE students SET fee_balance = fee_balance + OLD.amount WHERE id = OLD.student_id;
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER payments_apply
AFTER INSERT OR DELETE ON payments
FOR EACH ROW EXECUTE FUNCTION apply_payment();

SELECT fee_balance FROM students WHERE id = 8;                          -- 120000.00
INSERT INTO payments (student_id, amount, method) VALUES (8, 20000, 'cash') RETURNING id;
SELECT fee_balance FROM students WHERE id = 8;                          -- 100000.00

DELETE FROM payments WHERE student_id = 8 AND amount = 20000;
SELECT fee_balance FROM students WHERE id = 8;                          -- 120000.00 again

-- A payment larger than the balance is rejected by the CHECK constraint, and the INSERT is undone
INSERT INTO payments (student_id, amount, method) VALUES (8, 999999, 'cash');

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding