Trigger for audit logging in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger function that logs inserts.

PostgreSQL

CREATE OR REPLACE FUNCTION audit_insert() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(table_name, operation) VALUES ([1], 'INSERT'); RETURN NEW; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

A'my_table'

BNEW.table_name

CTG_TABLE_NAME

DOLD.table_name

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW or OLD which are row variables, not table name.

Hardcoding the table name.

The special variable TG_TABLE_NAME holds the name of the table that fired the trigger.

2fill in blank

medium

Complete the code to create a trigger that calls the audit function after insert.

PostgreSQL

CREATE TRIGGER audit_trigger AFTER INSERT ON [1] FOR EACH ROW EXECUTE FUNCTION audit_insert();

Drag options to blanks, or click blank then click option'

Aaudit_log

Bmy_table

Caudit_insert

Dinsert_log

Attempts:

3 left

💡 Hint

Common Mistakes

Using the audit log table name instead of the target table.

Using the function name as the table name.

The trigger must be created on the table you want to audit, here my_table.

3fill in blank

hard

Fix the error in the trigger function to correctly log the old row data on DELETE.

PostgreSQL

CREATE OR REPLACE FUNCTION audit_delete() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(table_name, operation, row_data) VALUES (TG_TABLE_NAME, 'DELETE', [1]); RETURN OLD; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

AOLD

BNEW

CTG_OP

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW instead of OLD.

Using TG_OP which is the operation name, not row data.

On DELETE triggers, OLD holds the row being deleted.

4fill in blank

hard

Fill both blanks to create a trigger function that logs UPDATE operations with old and new row data.

PostgreSQL

CREATE OR REPLACE FUNCTION audit_update() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(table_name, operation, old_data, new_data) VALUES ([1], 'UPDATE', [2], NEW); RETURN NEW; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

ATG_TABLE_NAME

BOLD

CNEW

DTG_OP

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW for old data.

Using TG_OP instead of table name.

TG_TABLE_NAME gives the table name, and OLD holds the old row data before update.

5fill in blank

hard

Fill all three blanks to create a trigger that logs all operations with operation type and row data.

PostgreSQL

CREATE OR REPLACE FUNCTION audit_all_ops() RETURNS trigger AS $$ BEGIN INSERT INTO audit_log(table_name, operation, row_data) VALUES ([1], [2], CASE WHEN TG_OP = 'DELETE' THEN [3] ELSE NEW END); RETURN CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

ATG_TABLE_NAME

BTG_OP

COLD

DNEW

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW instead of OLD for DELETE.

Using TG_OP as row data.

TG_TABLE_NAME is the table name, TG_OP is the operation type, and OLD is used for row data on DELETE operations.

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