Trigger for audit logging in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger that fires before an INSERT on the employees table.

SQL

CREATE TRIGGER audit_insert BEFORE INSERT ON employees FOR EACH ROW BEGIN INSERT INTO audit_log(action) VALUES([1]); END;

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

A'INSERT'

BNEW.id

C'UPDATE'

DOLD.name

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD values in an INSERT trigger

Not using quotes around string literals

The trigger should log the action type as 'INSERT' when a new row is inserted.

2fill in blank

medium

Complete the code to reference the new employee's name in the audit log during an INSERT.

SQL

CREATE TRIGGER audit_insert BEFORE INSERT ON employees FOR EACH ROW BEGIN INSERT INTO audit_log(employee_name) VALUES([1]); END;

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

ANEW.name

BOLD.name

CCURRENT_USER

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD instead of NEW in INSERT triggers

Using system functions instead of row data

Use NEW.name to get the name of the employee being inserted.

3fill in blank

hard

Fix the error in the trigger code to correctly log the old salary before an UPDATE.

SQL

CREATE TRIGGER audit_update BEFORE UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO audit_log(old_salary) VALUES([1]); END;

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

ANEW.salary

BCURRENT_TIMESTAMP

COLD.salary

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW.salary instead of OLD.salary

Using column name without prefix

OLD.salary refers to the salary before the update, which should be logged.

4fill in blank

hard

Fill both blanks to create a trigger that logs the action and timestamp on DELETE.

SQL

CREATE TRIGGER audit_delete BEFORE DELETE ON employees FOR EACH ROW BEGIN INSERT INTO audit_log(action, deleted_at) VALUES([1], [2]); END;

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

A'DELETE'

BNOW()

CCURRENT_DATE

D'REMOVE'

Attempts:

3 left

💡 Hint

Common Mistakes

Using CURRENT_DATE instead of NOW() for timestamp

Using wrong string for action

The action is 'DELETE' and the timestamp is the current date and time using NOW().

5fill in blank

hard

Fill all three blanks to create a trigger that logs the employee id, action, and current user on UPDATE.

SQL

CREATE TRIGGER audit_update_user BEFORE UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO audit_log(employee_id, action, changed_by) VALUES([1], [2], [3]); END;

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

AOLD.id

B'UPDATE'

CCURRENT_USER()

DNEW.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using NEW.id instead of OLD.id

Not quoting the action string

Using system functions incorrectly

OLD.id is the employee id before update, action is 'UPDATE', and CURRENT_USER() is the database user making the change.