Trigger for audit logging in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

💻 Command Output

intermediate

Output of audit trigger function on INSERT

Given the following PostgreSQL trigger function for audit logging, what will be the output in the audit table after inserting a new row into the main table?

PostgreSQL

CREATE TABLE main_table(id SERIAL PRIMARY KEY, data TEXT);
CREATE TABLE audit_log(id SERIAL PRIMARY KEY, operation TEXT, old_data TEXT, new_data TEXT, changed_at TIMESTAMP DEFAULT now());

CREATE OR REPLACE FUNCTION audit_trigger_func() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log(operation, old_data, new_data) VALUES ('INSERT', NULL, NEW.data);
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log(operation, old_data, new_data) VALUES ('UPDATE', OLD.data, NEW.data);
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log(operation, old_data, new_data) VALUES ('DELETE', OLD.data, NULL);
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON main_table
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

-- Now run:
INSERT INTO main_table(data) VALUES ('test data');
SELECT operation, old_data, new_data FROM audit_log ORDER BY id DESC LIMIT 1;

A operation: INSERT, old_data: NULL, new_data: test data

B operation: INSERT, old_data: test data, new_data: NULL

C operation: UPDATE, old_data: NULL, new_data: test data

D operation: DELETE, old_data: NULL, new_data: test data

💡 Hint

Think about what data is available during an INSERT operation in a trigger.

On INSERT, the OLD row does not exist, so old_data is NULL. The NEW row contains the inserted data, so new_data is the inserted value.

💻 Command Output

intermediate

Error raised by incorrect trigger function syntax

What error will this PostgreSQL trigger function produce when created?

PostgreSQL

CREATE OR REPLACE FUNCTION audit_trigger_func() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log(operation, old_data, new_data) VALUES ('INSERT', NULL, NEW.data);
    RETURN NEW;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

A SyntaxError: missing RETURN statement

B RuntimeError: NEW is undefined

C SyntaxError: missing semicolon after INSERT statement

D No error, function created successfully

💡 Hint

Check the end of the INSERT statement inside the function.

In PL/pgSQL, each statement must end with a semicolon. The INSERT statement is missing a semicolon before RETURN NEW, causing a syntax error.

❓ Configuration

advanced

Correct trigger creation for audit logging

Which option correctly creates a trigger on the table 'users' to call the audit logging function 'audit_trigger_func' after any UPDATE?

A CREATE TRIGGER audit_users BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE audit_trigger_func();

B CREATE TRIGGER audit_users AFTER UPDATE ON users FOR EACH STATEMENT EXECUTE FUNCTION audit_trigger_func();

C CREATE TRIGGER audit_users AFTER UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE audit_trigger_func();

D CREATE TRIGGER audit_users AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();

💡 Hint

Check the syntax for trigger creation in PostgreSQL 11+.

PostgreSQL 11+ uses EXECUTE FUNCTION, not EXECUTE PROCEDURE. The trigger must be AFTER UPDATE and FOR EACH ROW to log each row change.

❓ Troubleshoot

advanced

Why audit trigger does not log DELETE operations?

A developer notices that DELETE operations on 'orders' table are not logged in 'audit_log' even though the trigger is set for INSERT, UPDATE, DELETE. What is the most likely cause?

A The trigger function does not handle the DELETE case and returns NULL instead of OLD

B The DELETE operations are not committed to the database

C The audit_log table does not have a column for old_data

D The trigger is defined as BEFORE DELETE instead of AFTER DELETE

💡 Hint

Check the trigger function's code for DELETE handling.

If the trigger function does not return OLD on DELETE, the operation may not complete properly or the audit insert may not happen.

🔀 Workflow

expert

Order of steps to implement audit logging with triggers

What is the correct order of steps to implement audit logging on a PostgreSQL table using triggers?

A 2,1,3,4

B 1,2,3,4

C 1,3,2,4

D 3,2,1,4

💡 Hint

Think about dependencies: table must exist before function uses it, function before trigger, trigger before testing.

First create the audit table, then the function that inserts into it, then the trigger that calls the function, finally test the setup.