AFTER UPDATE triggers in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create an AFTER UPDATE trigger that logs changes.

MySQL

CREATE TRIGGER log_update AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO audit_log (employee_id, action) VALUES (NEW.id, [1]); END;

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

A'updated'

BOLD.id

CNEW.name

DNOW()

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD.id instead of a string for the action.

Using a column value instead of a descriptive string.

The trigger inserts the string 'updated' to indicate the action performed after an update.

2fill in blank

medium

Complete the code to reference the new salary value in the AFTER UPDATE trigger.

MySQL

CREATE TRIGGER salary_change AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO salary_log (employee_id, new_salary) VALUES (NEW.id, [1]); END;

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

AOLD.salary

BNEW.salary

CNEW.id

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD.salary which is the old value, not the new one.

Using just salary without NEW or OLD prefix.

NEW.salary refers to the updated salary value after the update.

3fill in blank

hard

Fix the error in the trigger code by choosing the correct keyword to start the trigger body.

MySQL

CREATE TRIGGER update_log AFTER UPDATE ON employees FOR EACH ROW [1] INSERT INTO audit (emp_id, change_time) VALUES (NEW.id, NOW()); END;

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

ADECLARE

BSTART

CBEGIN

DOPEN

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting BEGIN causes syntax errors.

Using START or DECLARE instead of BEGIN.

The trigger body must start with BEGIN and end with END to group statements.

4fill in blank

hard

Fill both blanks to correctly compare old and new salary values and log only if changed.

MySQL

CREATE TRIGGER check_salary AFTER UPDATE ON employees FOR EACH ROW BEGIN IF NEW.salary [1] OLD.salary THEN INSERT INTO salary_changes (emp_id, old_salary, new_salary) VALUES (NEW.id, OLD.salary, NEW.salary); END IF; END;

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

A=

B>

C!=

D<>

Attempts:

3 left

💡 Hint

Common Mistakes

Using = instead of not equal causes the condition to be wrong.

Using > only checks if new salary is greater, not if changed.

The operator <> means 'not equal' in SQL, used to check if salary changed.

5fill in blank

hard

Fill all three blanks to create a trigger that updates a timestamp after salary update.

MySQL

CREATE TRIGGER update_timestamp AFTER UPDATE ON employees FOR EACH ROW BEGIN UPDATE employees SET last_modified = [1] WHERE id = [2] AND salary [3] OLD.salary; END;

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

ANOW()

BNEW.id

C<>

DOLD.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD.id instead of NEW.id causes wrong row update.

Using = instead of <> misses changes.

NOW() sets current time, NEW.id identifies the row, and <> checks salary change.