MySQL BEFORE UPDATE Trigger

Master MySQL BEFORE UPDATE triggers — comparing OLD and NEW values, audit logging with full transition history, blocking disallowed changes via SIGNAL, and auto-setting last_modified columns. Covers NULL-safe equality (<=>) for column comparisons and when to prefer ON UPDATE CURRENT_TIMESTAMP over a trigger.

A BEFORE UPDATE trigger fires just before an existing row's new values are written. Both OLD (the row's current values) and NEW (the values it's about to take) are available — you can compare them, validate the change, or modify the new values before they land.

Because OLD is read-only and NEW is read/write, BEFORE UPDATE triggers are the canonical place to:

• Audit what changed — log the OLD-to-NEW transition
• Block disallowed changes — SIGNAL if the transition violates a rule
• Adjust the new values — auto-update timestamps, recompute derived columns

The classic audit-log pattern: a main table tracking the data that matters, plus a sibling table holding the change history.

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,
    old_salary        DECIMAL(10, 2),
    new_salary        DECIMAL(10, 2),
    action_timestamp  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Trigger that logs every salary change. Fires only when the salary actually changes — checking OLD.salary <> NEW.salary avoids noisy log entries when an UPDATE doesn't actually modify the salary column.

DROP TRIGGER IF EXISTS before_employee_update;

DELIMITER $$

CREATE TRIGGER before_employee_update
    BEFORE UPDATE ON employees
    FOR EACH ROW
BEGIN
    -- Only log when the salary actually changes
    IF OLD.salary <> NEW.salary
       OR (OLD.salary IS NULL AND NEW.salary IS NOT NULL)
       OR (OLD.salary IS NOT NULL AND NEW.salary IS NULL) THEN
        INSERT INTO audit_log (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
END$$

DELIMITER ;

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

-- Trigger an update
UPDATE employees SET salary = 55000 WHERE id = 1;

-- Inspect the audit log
SELECT * FROM audit_log;

The audit log captures the full transition: OLD.salary = 50000 → NEW.salary = 55000. Application code never had to remember to log; the trigger did it automatically.

Combine OLD/NEW comparison with SIGNAL to enforce policy — salaries can only go up:

DROP TRIGGER IF EXISTS no_salary_decrease;

DELIMITER $$

CREATE TRIGGER no_salary_decrease
    BEFORE UPDATE ON employees
    FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Salary cannot be decreased';
    END IF;
END$$

DELIMITER ;

UPDATE employees SET salary = 40000 WHERE id = 1;

The UPDATE is blocked. Note that this fires per row — if a single UPDATE affects 100 rows and one of them violates the rule, the entire UPDATE is rolled back (assuming you're inside a transaction or using a transactional storage engine like InnoDB).

Adjust the new value before it lands. Useful for keeping a last_modified timestamp accurate without trusting application code to set it:

ALTER TABLE employees ADD COLUMN last_modified TIMESTAMP NULL;

DROP TRIGGER IF EXISTS update_last_modified;

DELIMITER $$

CREATE TRIGGER update_last_modified
    BEFORE UPDATE ON employees
    FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END$$

DELIMITER ;

Now any UPDATE on employees automatically refreshes last_modified — the application can ignore that column entirely.

last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                        ON UPDATE CURRENT_TIMESTAMP

1. Always use DROP TRIGGER IF EXISTS at the top.
2. Compare OLD and NEW when logging — most UPDATEs change only some columns; logging unchanged values produces noise.
3. Handle NULL carefully in OLD/NEW comparisons — NULL <> NULL is NULL, not true. Use IS NULL branches or the <=> NULL-safe equality operator.
4. Don't log every event blindly — high-traffic tables produce vast audit logs that slow inserts and dominate storage. Be selective.
5. Combine validation and transformation in a single BEFORE trigger when both are needed — running through the row twice is wasteful.
6. Prefer ON UPDATE CURRENT_TIMESTAMP for last-modified columns — it's a feature, not a workaround.
7. Don't UPDATE the same table the trigger fires on. MySQL forbids it.

• BEFORE UPDATE triggers fire just before an UPDATE writes new values. Both OLD and NEW are available; NEW is writable.
• The canonical use cases: audit logging (capture OLD → NEW transitions), policy enforcement (SIGNAL on disallowed transitions), derived columns (auto-update last_modified, etc.).
• When logging, compare OLD and NEW so you don't log unchanged values; handle NULL transitions explicitly with IS NULL branches.
• For simple "set last_modified on update" use ON UPDATE CURRENT_TIMESTAMP — built-in feature, no trigger needed.
• SIGNAL inside a BEFORE UPDATE rolls back the UPDATE and any pending transaction work.