Trigger for audit logging in SQL - Step-by-Step Execution

Concept Flow - Trigger for audit logging

Data Change Event

↓

Trigger Fires

↓

Capture Old and New Data

↓

Insert Audit Record

↓

Original Operation Completes

When data changes, the trigger activates, captures old and new data, and inserts a record into the audit log before the original operation finishes.

Execution Sample

SQL

CREATE TRIGGER audit_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(employee_id, old_salary, new_salary, changed_at)
  VALUES (OLD.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

This trigger logs salary changes in the employees table into audit_log after each update.

Execution Table

Step	Event	Trigger Action	Data Captured	Audit Log Inserted
1	Update employee salary	Trigger fires after update	OLD.id=101, OLD.salary=5000, NEW.salary=5500	Insert audit_log(101, 5000, 5500, timestamp)
2	Audit record inserted	Audit log updated	Audit record with old and new salary saved	Audit log contains new entry
3	Original update completes	No trigger action	N/A	Audit log unchanged

💡 Trigger completes after inserting audit record; original update finishes successfully.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
OLD.id	101	101	101	101
OLD.salary	5000	5000	5000	5000
NEW.salary	5500	5500	5500	5500
audit_log entries	empty	empty	1 new record	1 new record

Key Moments - 2 Insights

Why does the trigger use OLD and NEW values?

The trigger uses OLD to get the data before the change and NEW to get the data after the change, so it can record what exactly was modified (see execution_table step 1).

When does the trigger run in relation to the update?

This trigger runs AFTER the update operation, ensuring the audit log records the final changed data (execution_table step 1 shows 'Trigger fires after update').

Visual Quiz - 3 Questions

Look at the execution table, what data does the trigger capture at step 1?

A Both old and new salary values

B Only the new salary value

C Only the old salary value

D No data is captured

💡 Hint

Check the 'Data Captured' column in execution_table row 1.

At which step is the audit log updated with a new record?

A Step 1

B Step 2

C Step 3

D No step updates the audit log

💡 Hint

Look at the 'Audit Log Inserted' column in execution_table row 2.

If the trigger was BEFORE UPDATE instead of AFTER UPDATE, what would change?

A The audit log would record the new data before the update happens

B The audit log would not record any data

C The audit log would record the old data before the update happens

D The trigger would not fire at all

💡 Hint

Consider when OLD and NEW values are available relative to the update operation.

Concept Snapshot

Trigger for audit logging syntax:
CREATE TRIGGER name AFTER UPDATE ON table
FOR EACH ROW
INSERT INTO audit_log(...) VALUES (OLD.col, NEW.col, CURRENT_TIMESTAMP);

Behavior:
- Fires after data change
- Captures old and new values
- Inserts audit record

Key rule: Use OLD and NEW to track changes.

Full Transcript

This visual execution shows how a trigger for audit logging works in SQL. When an update happens on the employees table, the trigger fires after the update. It captures the old and new salary values using OLD and NEW references. Then it inserts a record into the audit_log table with these values and the current timestamp. Finally, the original update operation completes. Variables like OLD.id and OLD.salary remain constant during the trigger, while the audit_log gains a new entry. Key points include understanding why OLD and NEW are used and when the trigger runs relative to the update. The quiz questions help reinforce these ideas by referencing the execution steps and variable changes.