BEFORE UPDATE and AFTER UPDATE Triggers

UPDATE triggers have access to both OLD (the row before the change) and NEW (the row after the change). This makes them ideal for change tracking and data validation scenarios.

Comparing OLD and NEW

Inside an UPDATE trigger, you can compare OLD.column and NEW.column to detect which values changed. This is essential for fine-grained audit logging — only recording changes, not entire row snapshots.

Common UPDATE Trigger Patterns

Automatically update an updated_at timestamp
Prevent changes to immutable fields (e.g., created_by)
Track field-level changes in an audit log
Sync derived fields when source fields change

A BEFORE UPDATE trigger can modify NEW values (e.g., automatically set updated_at = NOW()), saving you from having to include this in every application UPDATE query.

Example

DELIMITER //

CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
    IF OLD.email != NEW.email THEN
        SET NEW.email_verified = 0;
    END IF;
END //

CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO user_status_log (user_id, old_status, new_status, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NOW());
    END IF;
END //

DELIMITER ;

SQL

Full Editor

Pro Tip

Use OLD.column != NEW.column checks to only log fields that actually changed.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned