In this lesson, you will learn how to implement basic audit mechanisms to record "who" changed "what" and "when" within your database.
Why Track Changes?
When you perform an UPDATE or a DELETE, the original data is overwritten or removed. In professional environments, this is often insufficient. For security and compliance, you need a "paper trail." Audit logs allow you to reconstruct the history of a record, helping you investigate unauthorized changes or recover from accidental deletions.
Implementing Audit Columns
The simplest way to track changes is by adding metadata columns to your tables. Whenever a record is inserted or updated, your application (or a trigger) must populate these fields.
Consider an Employees table. You would add columns such as updated_at (a timestamp) and updated_by (the username).
ALTER TABLE Employees
ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN updated_by VARCHAR(50);
Whenever you modify a row, you include these columns in your UPDATE statement:
UPDATE Employees
SET salary = 75000,
updated_at = CURRENT_TIMESTAMP,
updated_by = 'admin_user'
WHERE employee_id = 101;
The Audit Table Pattern
If you need to know exactly what the value was before the change, you should use a separate Audit Table. This acts as a historical archive. Instead of just noting that a change happened, you copy the old row into an Employees_Audit table before applying the update.
This approach keeps your main table clean while ensuring that if a user asks, "What was this person's salary before the change?", you have an exact record of the previous state, the timestamp, and the user responsible for the action.
By combining updated_at columns for quick visibility and audit tables for deep historical analysis, you create a robust safety net for your data.
Key Takeaway: Audit logs transform your database from a simple current-state storage into a reliable historical record by capturing the metadata and previous states of your data.