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.