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

Concept Flow - Trigger for audit logging

Data Change Event

↓

Trigger Fires

↓

Trigger Function Executes

↓

Insert Audit Record

↓

Original Operation Completes

When a data change happens, the trigger activates, runs a function that logs the change, then the original operation finishes.

Execution Sample

PostgreSQL

CREATE FUNCTION audit_log() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_table VALUES (NEW.id, NEW.data, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
AFTER INSERT ON main_table
FOR EACH ROW EXECUTE FUNCTION audit_log();

This code creates a trigger function that logs inserts into an audit table whenever a new row is added to main_table.

Execution Table

Step	Event	Trigger Action	Audit Insert	Result
1	Insert row into main_table	Trigger fires after insert	Insert audit record with NEW row data and timestamp	Row inserted in main_table and audit_table
2	Insert row into main_table	Trigger fires after insert	Insert audit record with NEW row data and timestamp	Row inserted in main_table and audit_table
3	No more inserts	No trigger	No audit insert	Process ends

💡 No more insert events, trigger does not fire, audit logging stops

Variable Tracker

Variable	Start	After 1	After 2	Final
NEW.id	null	101	102	102
NEW.data	null	'First row'	'Second row'	'Second row'
now()	null	2024-06-01 10:00:00	2024-06-01 10:05:00	2024-06-01 10:05:00

Key Moments - 3 Insights

Why does the trigger function use RETURN NEW?

RETURN NEW passes the new row back to the database so the insert can complete successfully, as shown in execution_table step 1 and 2.

When exactly does the trigger fire?

The trigger fires AFTER the insert event on main_table, as shown in execution_table step 1 and 2 under 'Trigger Action'.

What happens if the audit insert fails?

If the audit insert fails, the whole insert operation rolls back, preventing inconsistent data, because triggers run within the same transaction.

Visual Quiz - 3 Questions

Look at the execution table, what is the audit insert action at step 2?

A Insert audit record with NEW row data and timestamp

B No audit insert

C Trigger does not fire

D Delete audit record

💡 Hint

Check the 'Audit Insert' column at row 2 in the execution_table

At which step does the trigger stop firing?

A Step 1

B Step 2

C Step 3

D Never stops

💡 Hint

Look at the 'Trigger Action' column in execution_table row 3

If the trigger function returned NULL instead of NEW, what would happen?

A The insert would succeed normally

B The insert would be canceled and no row added

C The audit record would not be inserted

D The trigger would fire twice

💡 Hint

Refer to key_moments about RETURN NEW and how it affects the insert operation

Concept Snapshot

Trigger for audit logging in PostgreSQL:
- Create a trigger function that inserts audit info
- Use AFTER INSERT trigger on target table
- Trigger fires on each row insert
- Function inserts audit record and returns NEW
- Ensures audit logs match data changes
- Trigger runs inside transaction, so failures rollback

Full Transcript

In PostgreSQL, audit logging can be done using triggers. When a row is inserted into a table, a trigger can fire after the insert event. This trigger runs a function that inserts a record into an audit table, capturing details like the new row's id, data, and the current timestamp. The trigger function must return NEW to allow the insert to complete. If the audit insert fails, the entire transaction rolls back, ensuring data consistency. The execution table shows each step: insert event, trigger firing, audit insert, and completion. Variables like NEW.id and NEW.data hold the new row's values during trigger execution. This method helps track changes automatically and reliably.