Why should you care?

Hospital or fintech audits ask for proof that staff did not alter patient billing rows retroactively.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE login_audit (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  db_user VARCHAR(80),
  client_host VARCHAR(120),
  event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  action_note VARCHAR(200)
);

CREATE TRIGGER trg_customer_change
AFTER UPDATE ON customers
FOR EACH ROW
INSERT INTO login_audit (db_user, client_host, action_note)
VALUES (CURRENT_USER(), SUBSTRING_INDEX(USER(), '@', -1),
        CONCAT('customer ', NEW.customer_id, ' name changed'));

What happened?

• Trigger writes audit row on customer update with CURRENT_USER and host.
• Combined with app user ids in separate column for full trace.

Practice next

1. CREATE login_audit and trigger.
2. UPDATE customer name; read audit.
3. Query audit by event_time range.
4. Add JSON column old_row/new_row for full diff.
5. Archive audit to cold storage monthly.

Remember

Capture actor, time, before/after. Restrict audit table to DBA role. Triggers are one pattern; binlog is another.

DataFlow SOC review

Security exports login_audit + order_audit for quarterly SOC2 evidence.

Outcome: Auditors verify change control without DB root access.