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.

Practice

1. What is the main purpose of a trigger in PostgreSQL for audit logging?

easy

A. To backup the database periodically

B. To automatically record changes made to data in a table

C. To create new tables automatically

D. To speed up query execution

Solution

1. Step 1: Understand what triggers do

   Triggers run code automatically when data changes occur in a table.

2. Step 2: Connect triggers to audit logging

   Audit logging means recording who changed what and when, which triggers help automate.

3. Final Answer:

   To automatically record changes made to data in a table -> Option B

4. Quick Check:

   Trigger = automatic audit record [OK]

Hint: Triggers run code on data changes to log audits [OK]

Common Mistakes:

• Thinking triggers speed up queries
• Confusing triggers with backups
• Assuming triggers create tables

2. Which of the following is the correct syntax to create a trigger function for audit logging in PostgreSQL?

easy

A. CREATE TRIGGER audit_log BEFORE INSERT ON audit_table EXECUTE FUNCTION log_changes();

B. CREATE FUNCTION audit_log() RETURNS void AS $$ BEGIN UPDATE audit_table SET changed = TRUE; END; $$ LANGUAGE sql;

C. CREATE FUNCTION audit_log() RETURNS trigger AS $$ BEGIN INSERT INTO audit_table VALUES (OLD.*); RETURN NEW; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION audit_log() RETURNS trigger AS $$ BEGIN INSERT INTO audit_table VALUES (NEW.*); RETURN OLD; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check function return type and language

   Trigger functions must return type 'trigger' and use 'plpgsql' language.

2. Step 2: Verify correct use of OLD and NEW

   For audit logging on updates/deletes, OLD.* is used to capture previous data; function returns NEW to continue operation.

3. Final Answer:

   CREATE FUNCTION audit_log() RETURNS trigger AS $$ BEGIN INSERT INTO audit_table VALUES (OLD.*); RETURN NEW; END; $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   Trigger function syntax = CREATE FUNCTION audit_log() RETURNS trigger AS $$ BEGIN INSERT INTO audit_table VALUES (OLD.*); RETURN NEW; END; $$ LANGUAGE plpgsql; [OK]

Hint: Trigger functions return 'trigger' and use plpgsql [OK]

Common Mistakes:

• Using RETURNS void instead of RETURNS trigger
• Returning OLD instead of NEW
• Wrong language like SQL instead of plpgsql

3. Given this trigger function and trigger creation:

CREATE FUNCTION audit_func() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(user_name, action_time) VALUES (current_user, now()); RETURN NEW; END; $$ LANGUAGE plpgsql;
CREATE TRIGGER audit_trigger AFTER INSERT ON employees FOR EACH ROW EXECUTE FUNCTION audit_func();

What happens when a new row is inserted into employees?

medium

A. A new row is added to audit_log with current user and timestamp

B. The insert into employees fails with an error

C. No action occurs because the trigger is AFTER INSERT

D. The employees row is deleted immediately

Solution

1. Step 1: Understand AFTER INSERT trigger behavior

   AFTER INSERT triggers run after a new row is added, so the insert succeeds first.

2. Step 2: Analyze trigger function actions

   The function inserts a row into audit_log with current user and timestamp, logging the event.

3. Final Answer:

   A new row is added to audit_log with current user and timestamp -> Option A

4. Quick Check:

   AFTER INSERT triggers log data after insert [OK]

Hint: AFTER INSERT triggers run after data is inserted [OK]

Common Mistakes:

• Thinking AFTER INSERT prevents insert
• Assuming trigger deletes data
• Believing no action happens after insert

4. You wrote this trigger function:

CREATE FUNCTION audit_changes() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log VALUES (NEW.*); RETURN NEW; END; $$ LANGUAGE plpgsql;

But when the trigger fires (e.g., on INSERT or UPDATE to the table), you get an error. What is the likely cause?

medium

A. Triggers cannot insert into tables

B. Trigger functions cannot use RETURN NEW

C. The function must be written in SQL, not plpgsql

D. The audit_log table does not match the NEW record structure

Solution

1. Step 1: Check compatibility of NEW.* with audit_log table

   NEW.* expands to all columns of the triggering table, which must match audit_log columns exactly.

2. Step 2: Identify mismatch causes error

   If audit_log has different columns or order, the insert fails when the trigger fires.

3. Final Answer:

   The audit_log table does not match the NEW record structure -> Option D

4. Quick Check:

   Column mismatch causes insert error [OK]

Hint: Ensure audit_log columns match NEW record exactly [OK]

Common Mistakes:

• Thinking RETURN NEW is invalid
• Assuming language must be SQL
• Believing triggers cannot insert data

5. You want to create an audit log that records old and new values on UPDATE for a products table. Which trigger function code correctly captures both old and new data for audit logging?

hard

A. CREATE FUNCTION audit_update() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(old_name, new_name) VALUES (OLD.name, NEW.name); RETURN NEW; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION audit_update() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(name) VALUES (NEW.name); RETURN OLD; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION audit_update() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(old_name, new_name) VALUES (NEW.name, OLD.name); RETURN NEW; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION audit_update() RETURNS trigger AS $$ BEGIN UPDATE audit_log SET name = NEW.name WHERE name = OLD.name; RETURN NEW; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Identify correct use of OLD and NEW in UPDATE triggers

   OLD contains previous row data, NEW contains updated data; audit log needs both.

2. Step 2: Check function logic and return value

   Insert old and new names correctly, then return NEW to allow update to proceed.

3. Final Answer:

   CREATE FUNCTION audit_update() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(old_name, new_name) VALUES (OLD.name, NEW.name); RETURN NEW; END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   OLD before, NEW after update [OK]

Hint: Use OLD for old data, NEW for new data in audit triggers [OK]

Common Mistakes:

• Swapping OLD and NEW values
• Returning OLD instead of NEW
• Using UPDATE instead of INSERT in audit log