BEFORE UPDATE triggers in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of BEFORE UPDATE trigger modifying a column

Consider a MySQL table employees with columns id, name, and salary. A BEFORE UPDATE trigger is set to increase the salary by 10% before any update.

What will be the salary of employee with id=1 after running this update?

UPDATE employees SET salary = 5000 WHERE id = 1;

MySQL

CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  SET NEW.salary = NEW.salary * 1.10;
END;

A 5500

B 5000

C 4500

D NULL

💡 Hint

The trigger changes the salary before the update is applied.

The BEFORE UPDATE trigger multiplies the new salary by 1.10, so 5000 becomes 5500 before saving.

📝 Syntax

intermediate

Identify the syntax error in BEFORE UPDATE trigger

Which option contains a syntax error in this BEFORE UPDATE trigger definition for MySQL?

MySQL

CREATE TRIGGER before_update_test
BEFORE UPDATE ON test_table
FOR EACH ROW
BEGIN
  SET NEW.value = OLD.value + 1;
END;

A CREATE TRIGGER before_update_test BEFORE UPDATE ON test_table FOR EACH ROW BEGIN SET NEW.value = OLD.value + 1; END;;

B CREATE TRIGGER before_update_test BEFORE UPDATE ON test_table FOR EACH ROW BEGIN SET NEW.value = OLD.value + 1; END

C CREATE TRIGGER before_update_test BEFORE UPDATE ON test_table FOR EACH ROW BEGIN SET NEW.value = OLD.value + 1 END;

D CREATE TRIGGER before_update_test BEFORE UPDATE ON test_table FOR EACH ROW BEGIN SET NEW.value = OLD.value + 1; END;

💡 Hint

Check for missing semicolons inside the BEGIN...END block.

Option C misses the semicolon after the SET statement, causing a syntax error.

❓ optimization

advanced

Optimizing BEFORE UPDATE trigger to avoid unnecessary updates

You have a BEFORE UPDATE trigger that sets NEW.updated_at = NOW() on every update. Which option optimizes the trigger to update updated_at only if some column other than updated_at changes?

MySQL

CREATE TRIGGER before_update_timestamp
BEFORE UPDATE ON records
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END;

A BEGIN IF OLD.updated_at = NEW.updated_at THEN SET NEW.updated_at = NOW(); END IF; END;

B BEGIN IF OLD <> NEW THEN SET NEW.updated_at = NOW(); END IF; END;

C BEGIN IF OLD.updated_at <> NEW.updated_at THEN SET NEW.updated_at = NOW(); END IF; END;

D BEGIN IF NOT(OLD.col1 <=> NEW.col1 AND OLD.col2 <=> NEW.col2) THEN SET NEW.updated_at = NOW(); END IF; END;

💡 Hint

Compare all relevant columns except updated_at to detect changes.

Option D compares specific columns (col1, col2) using NULL-safe equality to detect changes and update timestamp only then.

🔧 Debug

advanced

Why does this BEFORE UPDATE trigger cause an infinite loop?

Given this trigger, why does updating the status column cause an infinite loop?

CREATE TRIGGER before_update_status BEFORE UPDATE ON orders FOR EACH ROW BEGIN SET NEW.status = 'processed'; END;

A Because the trigger does not have a BEGIN...END block.

B Because the trigger modifies NEW.status unconditionally, causing the update to fire repeatedly.

C Because the trigger uses OLD.status instead of NEW.status.

D Because the trigger is AFTER UPDATE, not BEFORE UPDATE.

💡 Hint

Think about what happens when the trigger changes a column every time an update runs.

The trigger changes NEW.status every update, so MySQL sees a change and re-triggers the update, causing a loop.

🧠 Conceptual

expert

Understanding the order of execution in multiple BEFORE UPDATE triggers

If a MySQL table has two BEFORE UPDATE triggers defined, what is the guaranteed order of their execution?

A The order of execution is not guaranteed and can vary.

B The triggers execute in alphabetical order by trigger name.

C The triggers execute in the order they were created.

D The triggers execute in reverse order of creation.

💡 Hint

MySQL documentation does not specify a fixed order for multiple triggers of the same type.

MySQL does not guarantee the order of execution for multiple triggers of the same timing and event on a table.