Trigger for audit logging in SQL

Introduction

Triggers help automatically record changes in a database. Audit logging keeps track of who changed what and when.

You want to track changes to important data like user info or transactions.

You need to keep a history of changes for security or compliance reasons.

You want to know who updated or deleted records in your database.

You want to automatically save old data before it changes.

You want to create reports about data changes over time.

Syntax

SQL

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements to log changes
END;

Triggers run automatically when data changes happen on the specified table.

You can create triggers for INSERT, UPDATE, or DELETE actions.

Examples

This trigger logs every update on the users table by inserting a record into audit_log.

SQL

CREATE TRIGGER log_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(user_id, action, changed_at)
  VALUES (OLD.id, 'update', CURRENT_TIMESTAMP);
END;

This trigger logs every deletion from the orders table.

SQL

CREATE TRIGGER log_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(order_id, action, changed_at)
  VALUES (OLD.id, 'delete', CURRENT_TIMESTAMP);
END;

Sample Program

This example creates a simple users table and an audit_log table. The trigger log_user_update records every update on users. After updating Alice's name, the audit log shows the change.

SQL

CREATE TABLE audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(20),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TRIGGER log_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(user_id, action, changed_at)
  VALUES (OLD.id, 'update', CURRENT_TIMESTAMP);
END;

INSERT INTO users (id, name) VALUES (1, 'Alice');
UPDATE users SET name = 'Alice Smith' WHERE id = 1;

SELECT * FROM audit_log;

Important Notes

Use OLD to access the previous row data in UPDATE or DELETE triggers.

Use CURRENT_TIMESTAMP to record the exact time of the change.

Test triggers carefully to avoid performance issues on large tables.

Summary

Triggers automatically run on data changes to help with audit logging.

They record who changed data and when by inserting records into an audit table.

Use OLD and NEW keywords to access row data inside triggers.