How to implement an audit mechanism or change tracking (change history/logging) in SQL tables? What approaches are there, their pros and cons, and what is important to consider when designing such functionality?

Answer

An audit mechanism (log or change history) is implemented to track all data changes and ensure transparency. There are several main approaches:

1. Audit Triggers: Special AFTER/BEFORE INSERT/UPDATE/DELETE triggers that save the old and/or new state of a row in a separate audit table. Advantage — transparency for the application; disadvantage — potential slowdown of DML operations.

2. History Isolation in a Separate Table: Versioning of records is applied — when changing, the "old" row is copied to the history table with a timestamp. The main table holds only the current data.

3. Built-in Mechanisms (Change Data Capture, Temporal Tables): For example, in SQL Server, you can enable SYSTEM_VERSIONED TEMPORAL TABLE, and the DBMS will automatically store the history of changes.

Example (MySQL, audit trigger):

CREATE TABLE user_audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(10),
  old_value TEXT,
  new_value TEXT,
  changed_at DATETIME
);

DELIMITER //
CREATE TRIGGER audit_user_update
  AFTER UPDATE ON users
  FOR EACH ROW BEGIN
    INSERT INTO user_audit (user_id, action, old_value, new_value, changed_at)
    VALUES (OLD.id, 'UPDATE', OLD.name, NEW.name, NOW());
END//
DELIMITER ;

Trick Question

Question: Is it always sufficient to have an audit-trigger only on UPDATE if a complete change history is required?

Answer: No. A complete history can only be obtained by combining triggers on all types of operations: INSERT (log new rows), UPDATE (log changes), and DELETE (log deleted records).

Example:

-- You need to create 3 separate triggers for each operation, otherwise there will be "gaps" in the history.

Examples of Real Mistakes Due to Lack of Knowledge on the Topic

---

Story 1: The company used only an UPDATE trigger for auditing and for a year did not notice that deleted records were not being logged at all. As a result, when investigating an incident, they were unable to determine when an important user account was deleted.

---

Story 2: The project stored the old and new values of changes in one text field as serialized JSON, which greatly complicated the analysis — complex parsers and a separate DB replica for reporting were required.

---

Story 3: In a high-load DB, the developers implemented auditing through triggers without considering the growth of the log table size. Over time, the history table exceeded the main table by 50 times, leading to storage overflow and project-wide failures.

---