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

Challenge - 5 Problems

💻 Command Output

intermediate

What is the output of this audit trigger insertion?

Given a trigger that logs inserts into employees table, what will be the content of audit_log after inserting a new employee?

SQL

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE audit_log (action VARCHAR(10), emp_id INT, emp_name VARCHAR(50));

CREATE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
INSERT INTO audit_log VALUES ('INSERT', NEW.id, NEW.name);

INSERT INTO employees VALUES (1, 'Alice');

SELECT * FROM audit_log;

A

action | emp_id | emp_name
-------+--------+---------
INSERT | 1      | Alice

B

action | emp_id | emp_name
-------+--------+---------
INSERT | NULL   | NULL

C No rows returned

D Syntax error in trigger definition

💡 Hint

Remember that NEW keyword in triggers refers to the inserted row.

The trigger fires after an insert on employees. It inserts a row into audit_log with the action 'INSERT' and the new employee's id and name. So the audit_log table will have one row showing the inserted employee.

❓ Configuration

intermediate

Which trigger definition correctly logs DELETE actions?

Choose the correct SQL trigger that logs deletions from employees into audit_log with action 'DELETE'.

A CREATE TRIGGER log_employee_delete BEFORE DELETE ON employees FOR EACH ROW INSERT INTO audit_log VALUES ('DELETE', NEW.id, NEW.name);

B CREATE TRIGGER log_employee_delete AFTER DELETE ON employees FOR EACH ROW INSERT INTO audit_log VALUES ('DELETE', NEW.id, NEW.name);

C CREATE TRIGGER log_employee_delete AFTER DELETE ON employees FOR EACH ROW INSERT INTO audit_log VALUES ('DELETE', OLD.id, OLD.name);

D CREATE TRIGGER log_employee_delete BEFORE DELETE ON employees FOR EACH ROW INSERT INTO audit_log VALUES ('DELETE', OLD.id, OLD.name);

💡 Hint

For DELETE triggers, OLD holds the row being deleted.

After DELETE triggers run after the row is deleted, and OLD contains the deleted row's data. Using NEW in DELETE triggers is invalid. The correct syntax uses AFTER DELETE and OLD.

❓ Troubleshoot

advanced

Why does this audit trigger fail to log updates?

Given this trigger, why does it fail to log the updated data in audit_log after updating employees?

SQL

CREATE TRIGGER log_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO audit_log VALUES ('UPDATE', OLD.id, OLD.name);

A The trigger is AFTER UPDATE but should be BEFORE UPDATE.

B The trigger uses OLD values instead of NEW values for updated data.

C The trigger syntax is invalid because it lacks BEGIN...END block.

D The audit_log table does not exist.

💡 Hint

Consider which values reflect the updated data after an update.

After an update, OLD holds the old data before update, NEW holds the new data. To log the updated values, the trigger should insert NEW values, not OLD. Using OLD logs old data, which might be confusing or appear as no change.

🔀 Workflow

advanced

Order the steps to create an audit logging trigger for INSERT and UPDATE

Put these steps in the correct order to create a trigger that logs both INSERT and UPDATE actions on employees.

A 3,2,1,4

B 2,1,3,4

C 1,3,2,4

D 1,2,3,4

💡 Hint

Think about what you need before creating triggers and testing.

First create the audit_log table to store logs. Then write the trigger function that inserts logs. Next create the trigger that calls this function on insert or update. Finally, test the setup.

✅ Best Practice

expert

Which approach best ensures audit logging does not block main transactions?

In a high-traffic database, which method best prevents audit logging triggers from slowing down main data changes?

A Use asynchronous logging by writing audit entries to a queue table and processing them later

B Write audit logs synchronously inside the trigger using INSERT statements

C Disable triggers during peak hours to improve performance

D Log audit data only once per day using a scheduled batch job

💡 Hint

Consider how to avoid slowing down the main transaction.

Writing audit logs synchronously inside triggers can slow down transactions. Disabling triggers or batching once a day risks losing audit detail or timeliness. Using asynchronous logging with a queue table lets main transactions finish quickly, and audit logs are processed separately.