Skip to content
Tutorials Center
My learning

Topics

Lesson 69 of 75 ยท Projects

Project: Audit Trail and History Tables

Record who changed what in MySQL: audit triggers, valid-from and valid-to history tables, point-in-time queries, restoring an old version and retention.

Sooner or later someone asks "who changed this price?", "what did this customer's plan look like in March?" or "can we undo that edit?". A normal table cannot answer, because an UPDATE overwrites the old value and a DELETE removes the row. If you did not record the change when it happened, it is gone.

This project adds memory to a MySQL database. You will build three tools: an audit log that records every change (who, when, old and new values), a history table with validity periods that lets you ask what was true on any date, and a version table that supports restoring an earlier state. You will also plan how long to keep all this data. Related lessons are triggers, schema design patterns and events.

What runs live here. SQLite supports triggers and JSON functions, so the trigger examples run live with SQLite syntax. MySQL's trigger syntax is different (DELIMITER, SET, JSON_OBJECT, session variables) and is shown in a read-only block. MySQL has no built-in system-versioned tables (MariaDB does), so history is something you build yourself, as here.

Three questions, three tools

Audit log Who changed what, and when?
History table What was true on a given date?
Versions Can we go back to an earlier state?
1 / 2

Trigger or application code?

You can write audit rows from the application (every service that changes data also inserts a log row) or from database triggers. Each has a weakness.

  • Application-level auditing knows the logged-in user, the request id and the business reason ("customer asked for refund"). But it only records changes that go through that code. A script, a second service, or someone fixing data by hand in a client skips it.
  • Trigger-based auditing records every change made by any client, because the trigger lives in the database. But a trigger does not know who the application user is: it only sees the database account, which is often one shared account for the whole app. And it adds work to every write.

A common combination: triggers capture the data change, and the application tells the trigger who is acting by setting a session variable at the start of each request (SET @app_user = 'mia'). The trigger reads @app_user. The SQLite stand-in below uses a one-row app_context table for the same purpose.

Step 1: an audit log with triggers

The audit table is generic. One row per change, with the table name, the primary key, the action, and the old and new row as JSON. Three triggers per audited table (insert, update, delete) fill it.

SQL Audit triggers for insert, update and delete

Press Run (or Ctrl + Enter) to see the result here.

Three rows are logged, not four. The insert by mia has only a new row. The update by omar has both the old and the new plan. The second identical update is ignored because the WHEN clause compares old and new values with IS NOT, which treats NULL correctly (a plain <> would give NULL and skip changes involving nulls). The delete keeps the full old row, which is what makes a deleted record recoverable.

The same logic in MySQL looks like this. It is read-only here, because the browser engine is SQLite:

SQL
DELIMITER //

CREATE TRIGGER customers_au AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
  IF NOT (OLD.name <=> NEW.name AND OLD.email <=> NEW.email AND OLD.plan <=> NEW.plan) THEN
    INSERT INTO audit_log (table_name, row_id, action, changed_by, old_row, new_row)
    VALUES ('customers', NEW.id, 'UPDATE', COALESCE(@app_user, CURRENT_USER()),
            JSON_OBJECT('name', OLD.name, 'email', OLD.email, 'plan', OLD.plan),
            JSON_OBJECT('name', NEW.name, 'email', NEW.email, 'plan', NEW.plan));
  END IF;
END//

DELIMITER ;

Notes for MySQL: the null-safe operator <=> plays the role of SQLite's IS; MySQL has no WHEN condition on triggers, so the check is an IF inside the body; CURRENT_USER() is the fallback when the application did not set @app_user; and MySQL allows several triggers for the same event on a table (FOLLOWS and PRECEDES set their order). Triggers do not fire for cascaded foreign-key actions in InnoDB, which is a known gap: a ON DELETE CASCADE child delete is not audited by the child's trigger.

Keep the audit log in the same transaction as the change (triggers do this automatically). If the change rolls back, so does its audit row, and that is the behaviour you want.

Step 2: a history table with validity periods

An audit log answers "what happened". A history table (also called a temporal or "slowly changing dimension" table) answers "what was the state on this date". Each row carries valid_from and valid_to; the current row has valid_to IS NULL. Treat the interval as half-open: a row is valid from valid_from up to, but not including, valid_to. That way the end of one row equals the start of the next, with no gaps and no overlaps.

SQL Price history and an as-of query

Press Run (or Ctrl + Enter) to see the result here.

The first query lists three rows: 450 from January, 500 from March, 550 from June (still open, valid_to is NULL). The as-of query returns 450 for 2026-02-15, 500 for 2026-03-01 (the boundary day belongs to the new row, thanks to the half-open interval) and 550 for 2026-07-01.

Two practical points. First, a price change must be one transaction (close the old row, insert the new one), otherwise a crash in between leaves a gap or two open rows. Second, order lines already store their own price (see the orders project); a history table is for questions like "what did the list price look like last spring?", not for invoicing. In MySQL, use DATE or DATETIME columns and a far-future valid_to such as '9999-12-31' if you prefer to avoid IS NULL checks, because it lets a plain index range scan work on both bounds.

Step 3: versions and restore

For content such as pages, articles or settings, users want "restore version 3". Keep the current row in the main table with a version number, and copy each replaced state into a versions table with a trigger. Restoring never rewrites history. It is a new update that copies old values into the current row, so the restore itself becomes a version and can be undone.

SQL Version snapshots and restore

Press Run (or Ctrl + Enter) to see the result here.

After the restore the page is back to Hello, but its version is 4, not 1. The versions table holds three snapshots: version 1 (Hello), version 2 (Hello world) and version 3 (Hello, brave world!). The state that existed just before the restore was saved too, so the restore can itself be reverted. In MySQL the restore is a multi-table update: UPDATE pages p JOIN page_versions v ON v.page_id = p.id AND v.version = 1 SET p.title = v.title, p.body = v.body, p.version = p.version + 1 WHERE p.id = 1.

To avoid two editors overwriting each other, make the update conditional on the version the editor started from: WHERE id = 1 AND version = 3. If it affects zero rows, someone else saved first. This pattern is called optimistic locking.

Step 4: retention

Audit and history data only grows. Decide up front how long you keep it, because unlimited retention means an ever-growing table, slower backups and, for personal data, possible legal trouble. Typical rules: keep a year of detailed audit rows, keep history rows forever if they are small, and archive older audit rows to cheaper storage or delete them.

Delete in small batches. One DELETE of 50 million rows holds locks, fills the undo log and makes replicas lag. Many small deletes, each in its own transaction, keep the system responsive. The demo uses batches of three:

SQL Purging old audit rows in batches

Press Run (or Ctrl + Enter) to see the result here.

The batches delete 3, then 2, then 0 rows. A job loops until a batch returns 0. Eight rows become three, and the oldest remaining date is 2026-04-01. MySQL lets you write DELETE FROM audit_log WHERE changed_at < '2026-01-01' ORDER BY id LIMIT 10000 directly, with no subquery (INSERT, UPDATE and DELETE in MySQL). Run it from a scheduled event or from cron.

For large audit tables, partitioning by month makes retention almost free: dropping an old partition is instant, where deleting rows is slow (partitioning). Also keep the audit table small by indexing only what you query, usually (table_name, row_id) for "history of this record" and changed_at for purging.

A word on privacy and integrity

  • An audit log copies data, including personal data. Decide whether to log sensitive columns at all, mask them, or log only the fact that they changed.
  • Protect the log from the people it watches. Give the application account INSERT and SELECT on the audit table but no UPDATE or DELETE, and let only a maintenance account purge it (users, privileges and roles).
  • The MySQL binary log records changes too, but it exists for replication and point-in-time recovery. It is not a queryable, user-attributed audit trail.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main advantage of trigger-based auditing over application-level auditing?

    A trigger lives in the database, so every writer fires it. The application can add the acting user through a session variable.
  2. 2. In a history table with valid_from and valid_to, which interval convention avoids gaps and overlaps?

    With a half-open interval the end of one row is the start of the next, so every day belongs to exactly one row.
  3. 3. How should you restore an earlier version of a page?

    Restoring as a new update keeps history intact and makes the restore itself reversible.
  4. 4. Why purge old audit rows in small batches?

    A huge delete runs as one big transaction. Small batches keep each transaction short.
  5. 5. Why can an UPDATE trigger not tell you which application user made a change on its own?

    Applications usually connect with one database account. Pass the real user in a session variable such as @app_user.

Summary

  • An audit log records each change with table, key, action, user, time, and old and new values (often as JSON).
  • Triggers capture every writer. The application supplies the real user through a session variable. Use null-safe comparison (<=> in MySQL) to skip no-op updates.
  • A history table with half-open valid_from/valid_to periods answers "what was true on this date" with one join condition.
  • Snapshot versions on update and restore by writing the old values as a new version. Use a version column for optimistic locking.
  • Decide retention early, purge in batches, consider partitions, and restrict who may change the log.

Next: Project: Multi-Tenant SaaS Schema.