PL/pgSQL BEFORE/AFTER UPDATE Triggers

Master UPDATE triggers — BEFORE for validation/normalization (can modify NEW), AFTER for audit logging (cannot modify NEW). Critical fixes: original INSERT was missing VALUES keyword (syntax error), CREATE TRIGGER had two ON clauses (syntax error), and AFTER UPDATE trigger tried to modify NEW.last_updated which has no effect — that pattern requires BEFORE UPDATE. Uses IS DISTINCT FROM for NULL-safe column comparisons.

UPDATE triggers fire when an UPDATE statement modifies rows in a table. The two timing variants serve very different purposes:

DROP TABLE IF EXISTS employees CASCADE;

CREATE TABLE employees (
    id           SERIAL PRIMARY KEY,
    name         VARCHAR(100),
    salary       NUMERIC,
    last_updated TIMESTAMP
);

INSERT INTO employees (name, salary) VALUES
    ('Alice',   50000),
    ('Bob',     60000),
    ('Charlie', 55000);

BEFORE UPDATE triggers are the right place for input checks. The salary-can't-be-negative rule is a classic example:

CREATE OR REPLACE FUNCTION check_salary()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.salary < 0 THEN
        RAISE EXCEPTION 'Salary cannot be negative (got %)', NEW.salary;
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER before_update_salary
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION check_salary();

-- Should fail: trigger raises an exception
UPDATE employees SET salary = -1000 WHERE id = 1;

-- Should succeed: passes the check
UPDATE employees SET salary = 70000 WHERE id = 1;
SELECT * FROM employees WHERE id = 1;

Negative rejected, positive accepted. Note that last_updated is still NULL — neither this trigger nor anything else has set it. Let's fix that next.

This is the canonical reason to use a BEFORE trigger to modify NEW: keep a last_updated column current automatically. The application doesn't have to remember; the database guarantees it.

CREATE OR REPLACE FUNCTION set_last_updated()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.last_updated := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;

CREATE TRIGGER employees_set_last_updated
    BEFORE UPDATE ON employees
    FOR EACH ROW
    EXECUTE FUNCTION set_last_updated();

UPDATE employees SET salary = 75000 WHERE id = 1;
SELECT id, name, salary, last_updated FROM employees WHERE id = 1;

The right use for AFTER triggers: react to changes that have already happened. Audit logging is the canonical case.

CREATE TABLE IF NOT EXISTS salary_audit (
    audit_id   SERIAL PRIMARY KEY,
    employee_id INT,
    old_salary NUMERIC,
    new_salary NUMERIC,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- Only log when salary actually changed
    IF NEW.salary IS DISTINCT FROM OLD.salary THEN
        INSERT INTO salary_audit (employee_id, old_salary, new_salary)
        VALUES (NEW.id, OLD.salary, NEW.salary);
    END IF;

    RETURN NEW;   -- ignored for AFTER triggers, but RETURN NEW is convention
END;
$$;

CREATE TRIGGER after_update_salary
    AFTER UPDATE ON employees
    FOR EACH ROW
    WHEN (NEW.salary IS DISTINCT FROM OLD.salary)
    EXECUTE FUNCTION log_salary_change();

Two safety nets here:

• WHEN (NEW.salary IS DISTINCT FROM OLD.salary) — the trigger doesn't even fire when salary is unchanged. Cheaper than running the function and bailing out inside.
• The same check inside the function as defensive backup, in case the trigger gets re-created without the WHEN clause.

UPDATE employees SET salary = 80000 WHERE id = 1;
UPDATE employees SET salary = 80000 WHERE id = 1;  -- no change → no audit row
UPDATE employees SET salary = 65000 WHERE id = 2;

SELECT * FROM salary_audit ORDER BY audit_id;

Two audit entries — one for each genuine change. The unchanged-salary update didn't make it past the WHEN clause, so it didn't fire the trigger.

1. Modifying NEW only works in BEFORE triggers. AFTER triggers can read NEW but can't change what was written.
2. For audit logging, use IS DISTINCT FROM — handles NULL correctly. NEW.col != OLD.col returns NULL when either side is NULL, treating it as not-different and silently failing to audit.
3. Filter with WHEN before the function fires — saves the function-call overhead when the change isn't relevant.
4. Validate with CHECK constraints when possible. CHECK (salary >= 0) is faster, simpler, and visible in the schema. Use BEFORE triggers for rules CHECK can't express.
5. Keep BEFORE trigger functions fast. They run on the hot path of every UPDATE; complex logic slows every write.
6. Return NEW from BEFORE UPDATE triggers by convention. Return NULL deliberately when you mean to skip the row.

• BEFORE UPDATE triggers fire before the write. They can validate, modify NEW, or return NULL to skip. They run on the hot write path.
• AFTER UPDATE triggers fire after the write. They can react to the change but cannot modify NEW. They're the right place for audit logging and cascading actions.
• Common bug: trying to set NEW.last_updated from an AFTER trigger — has no effect. Must be BEFORE.
• Use IS DISTINCT FROM instead of != for NULL-safe column comparisons.
• Use WHEN clauses to filter before the function runs, saving overhead when the change isn't interesting.
• For simple constraints like "salary >= 0," prefer a CHECK constraint over a trigger — faster, declarative, visible in the schema.