Write an audit trigger

Log changes with an audit trigger.

Audit trigger

An audit trigger records table changes to a log table. It answers who, what, when.

CREATE TRIGGER audit_product
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, old_value, new_value, changed_at)
  VALUES ('products', OLD.name, NEW.name, NOW());
END;

This query writes changes to an audit table.

The trigger audit_product is created.

Audit trigger — details

An audit trigger keeps a change history. Used for investigation and compliance.

An entry usually holds the table name, the old and new values, the user who made the change and a timestamp from NOW().

Exercise Audit trigger

Write an audit_product trigger: record changes to audit_log.

Show example solution

CREATE TRIGGER audit_product
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, old_value, new_value, changed_at)
  VALUES ('products', OLD.name, NEW.name, NOW());
END

Test yourself

Add NOW

  VALUES ('products', OLD.name, NEW.name, ____());

Show answer

  VALUES ('products', OLD.name, NEW.name, NOW());

Write an audit trigger: match the terms

audit

audit
log

record
who/what/when

question
change

history
What does an audit trigger record?
- Indexes
- The query plan
- The changes
- Views
Show answer

The changes — An audit trigger records changes.

Next lesson: Summary - Trigger

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies