MySQL AFTER UPDATE Trigger

Master MySQL AFTER UPDATE triggers — per-column audit logging with NULL-safe equality (NOT (OLD.col <=> NEW.col)), cascading name changes to denormalized tables, and the BEFORE-vs-AFTER UPDATE decision. Covers the cost of cascading triggers on bulk operations.

An AFTER UPDATE trigger fires after the row's new values have been written. Both OLD and NEW are available — read-only — letting you see exactly what changed. The row in the table now reflects NEW, but you have full visibility into the transition that just happened.

Compared to BEFORE UPDATE: you can't modify the new values (the write already happened) and you can't reject the update (it's already in the table). What you can do is react to the change with downstream effects.

For audit logging specifically, either timing works — the captured OLD and NEW values are identical. AFTER is conventional for audit because it implies "this change actually happened." If a separate BEFORE UPDATE trigger SIGNALs and aborts the change, an AFTER UPDATE log entry isn't created.

DROP TABLE IF EXISTS audit_log;
DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(100),
    salary  DECIMAL(10, 2)
);

CREATE TABLE audit_log (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    employee_id       INT,
    column_changed    VARCHAR(50),
    old_value         TEXT,
    new_value         TEXT,
    action_timestamp  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by        VARCHAR(100)
);

INSERT INTO employees (name, salary) VALUES
    ('John Doe',   50000),
    ('Jane Smith', 60000);

A more sophisticated audit pattern than just "salary changed" — log which column changed, with old and new values cast to TEXT for a generic schema. This way the same audit table works for any UPDATE on any column:

DROP TRIGGER IF EXISTS after_employee_update;

DELIMITER $$

CREATE TRIGGER after_employee_update
    AFTER UPDATE ON employees
    FOR EACH ROW
BEGIN
    -- Log salary changes
    IF NOT (OLD.salary <=> NEW.salary) THEN
        INSERT INTO audit_log (employee_id, column_changed, old_value, new_value, changed_by)
        VALUES (NEW.id, 'salary', OLD.salary, NEW.salary, CURRENT_USER());
    END IF;

    -- Log name changes
    IF NOT (OLD.name <=> NEW.name) THEN
        INSERT INTO audit_log (employee_id, column_changed, old_value, new_value, changed_by)
        VALUES (NEW.id, 'name', OLD.name, NEW.name, CURRENT_USER());
    END IF;
END$$

DELIMITER ;

UPDATE employees SET salary = 55000           WHERE id = 1;
UPDATE employees SET name   = 'Jane Williams' WHERE id = 2;
UPDATE employees SET salary = 65000, name = 'JD' WHERE id = 1;

SELECT * FROM audit_log ORDER BY id;

Notice the third UPDATE changed both salary and name — and produced two audit rows (one per changed column), both with the same timestamp. That's the per-row, per-column granularity this pattern gives you.

When an employee's name changes, propagate the new name to other tables that copied it. Sometimes denormalization is faster to query than always joining — but it requires triggers (or careful application code) to stay in sync:

DROP TABLE IF EXISTS notifications;

CREATE TABLE notifications (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    employee_id     INT,
    employee_name   VARCHAR(100),
    message         TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO notifications (employee_id, employee_name, message) VALUES
    (1, 'John Doe', 'Welcome aboard!'),
    (1, 'John Doe', 'Reminder: HR review next Friday');

DROP TRIGGER IF EXISTS sync_employee_name;

DELIMITER $$

CREATE TRIGGER sync_employee_name
    AFTER UPDATE ON employees
    FOR EACH ROW
BEGIN
    IF NOT (OLD.name <=> NEW.name) THEN
        UPDATE notifications
        SET    employee_name = NEW.name
        WHERE  employee_id   = NEW.id;
    END IF;
END$$

DELIMITER ;

UPDATE employees SET name = 'Jonathan Doe' WHERE id = 1;

SELECT * FROM notifications WHERE employee_id = 1;

Both notification rows now reflect the new name. The cascade happened automatically — application code only ran UPDATE employees.

1. Use DROP TRIGGER IF EXISTS at the top of every deployment script.
2. Use NULL-safe equality (<=>) when comparing OLD and NEW — handles columns that can be NULL.
3. Log only when something actually changed — comparing OLD and NEW prevents noisy log entries from no-op UPDATEs.
4. Capture metadata — timestamp and CURRENT_USER() in audit logs.
5. For per-column audit, an IF block per column gives clean granular history. Stash old/new as TEXT in a generic audit schema.
6. Watch the cascade cost. Triggers that UPDATE other tables can amplify work; on bulk operations the multiplication can become severe.
7. Don't update the same table — MySQL forbids it.

• AFTER UPDATE triggers fire after the row's new values are written. Both OLD and NEW are available, read-only.
• Use AFTER UPDATE for downstream effects: audit logging, cascading to related tables, summary-table maintenance.
• Use BEFORE UPDATE for validation, modification, or aborting the update — those need the writable NEW.
• Use NULL-safe equality (<=>) for OLD-vs-NEW comparisons; NOT (OLD.x <=> NEW.x) reads as "x actually changed (including NULL transitions)".
• Cascading triggers can multiply work on bulk operations — measure performance.