Practical Trigger: Audit Log

One of the most valuable real-world uses of triggers is building an automatic audit log. An audit log records every change made to important data, including what changed, who changed it, and when — without requiring any modification to application code.

Design

Create an audit table with columns for the table name, the action (INSERT/UPDATE/DELETE), the affected record ID, a JSON snapshot of old and new values, and a timestamp. Then attach AFTER triggers to each important table.

Benefits

Captures changes even when made directly via SQL (bypassing the application layer)
Requires no application code changes
Provides a reliable history for compliance and debugging

Storing old and new values as JSON (using JSON_OBJECT) provides a flexible schema that works for any table without requiring a separate audit table per table.

Example

CREATE TABLE IF NOT EXISTS audit_log (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tbl        VARCHAR(64),
    action     ENUM('INSERT','UPDATE','DELETE'),
    record_id  BIGINT,
    old_data   JSON,
    new_data   JSON,
    changed_at DATETIME DEFAULT NOW()
);

DELIMITER //
CREATE TRIGGER after_products_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (tbl, action, record_id, old_data, new_data)
    VALUES (
        'products', 'UPDATE', NEW.id,
        JSON_OBJECT('price', OLD.price, 'stock', OLD.stock),
        JSON_OBJECT('price', NEW.price, 'stock', NEW.stock)
    );
END //
DELIMITER ;

SQL

Full Editor

Pro Tip

Use JSON_OBJECT() in triggers to store flexible snapshots of row data without needing a schema per table.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned