AFTER UPDATE triggers in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of AFTER UPDATE trigger modifying another table

Given two tables employees and audit_log, and an AFTER UPDATE trigger on employees that inserts a record into audit_log whenever an employee's salary changes, what will be the content of audit_log after this update?

UPDATE employees SET salary = salary + 1000 WHERE id = 3;

MySQL

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), salary INT);
CREATE TABLE audit_log (log_id INT AUTO_INCREMENT PRIMARY KEY, emp_id INT, old_salary INT, new_salary INT);

DELIMITER $$
CREATE TRIGGER trg_after_update_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary <> NEW.salary THEN
    INSERT INTO audit_log(emp_id, old_salary, new_salary) VALUES (OLD.id, OLD.salary, NEW.salary);
  END IF;
END$$
DELIMITER ;

INSERT INTO employees VALUES (3, 'Alice', 5000);

A One row in audit_log: emp_id=3, old_salary=5000, new_salary=6000

B No rows in audit_log

C One row in audit_log: emp_id=3, old_salary=6000, new_salary=6000

D Multiple rows in audit_log with old_salary=5000

💡 Hint

The trigger runs after the update and compares old and new salary values.

The AFTER UPDATE trigger checks if the salary changed. Since the salary increased from 5000 to 6000, it inserts one row recording the old and new salaries.

🧠 Conceptual

intermediate

Understanding WHEN AFTER UPDATE triggers fire

Which of the following statements about AFTER UPDATE triggers in MySQL is true?

A AFTER UPDATE triggers fire before the update operation starts.

B AFTER UPDATE triggers fire after each row is updated.

C AFTER UPDATE triggers can modify the row being updated directly.

D AFTER UPDATE triggers do not have access to OLD and NEW row values.

💡 Hint

Think about when the trigger runs relative to the update and what data it can access.

AFTER UPDATE triggers run after each row is updated and have access to OLD and NEW values. They cannot modify the row being updated but can affect other tables.

📝 Syntax

advanced

Identify the syntax error in AFTER UPDATE trigger

Which option contains a syntax error in this AFTER UPDATE trigger definition?

MySQL

CREATE TRIGGER trg_after_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(emp_id, old_salary, new_salary) VALUES (OLD.id, OLD.salary, NEW.salary);
END;

A Missing DELIMITER change before and after trigger definition

B Trigger must be BEFORE UPDATE, not AFTER UPDATE

C OLD and NEW cannot be used in AFTER UPDATE triggers

D FOR EACH ROW should be FOR EACH RECORD

💡 Hint

MySQL requires changing the delimiter when defining triggers with multiple statements.

In MySQL, when defining triggers with BEGIN...END blocks, you must change the delimiter temporarily to avoid syntax errors.

🔧 Debug

advanced

Why does this AFTER UPDATE trigger cause an infinite loop?

Consider this trigger on table accounts that updates the last_modified column after any update:

CREATE TRIGGER trg_after_update_accounts AFTER UPDATE ON accounts FOR EACH ROW BEGIN UPDATE accounts SET last_modified = NOW() WHERE id = NEW.id; END;

What is the cause of the infinite loop?

A AFTER UPDATE triggers cannot contain UPDATE statements.

B The trigger uses NOW() which is not allowed in triggers.

C The trigger lacks a WHERE clause to limit updates.

D The trigger updates the same table, causing it to fire repeatedly.

💡 Hint

Think about what happens when the trigger updates the same table it is defined on.

The trigger updates the same row in the table, causing the trigger to fire again and again, creating an infinite loop.

❓ optimization

expert

Optimizing AFTER UPDATE trigger to avoid unnecessary writes

You have an AFTER UPDATE trigger that logs changes only if the status column changes. Which option is the most efficient way to write the trigger to avoid unnecessary inserts?

A Check status change outside the trigger in application code before updating.

B Use BEFORE UPDATE trigger instead of AFTER UPDATE.

C Use IF OLD.status <> NEW.status THEN INSERT ... END IF; inside the trigger.

D INSERT INTO log_table VALUES (...) unconditionally in the trigger.

💡 Hint

Triggers can check old and new values to decide if action is needed.

Using an IF condition inside the trigger to compare OLD and NEW values prevents unnecessary inserts and improves performance.