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.

Practice

1. What is the main purpose of a BEFORE UPDATE trigger in MySQL?

easy

A. To execute code before a row is updated, allowing data validation or modification.

B. To execute code after a row is updated, for logging changes.

C. To prevent any updates on a table permanently.

D. To create a backup of the table before updating.

Solution

1. Step 1: Understand trigger timing

   BEFORE UPDATE triggers run before the data is changed in the table.

2. Step 2: Purpose of the trigger

   This timing allows checking or modifying the new data before it is saved.

3. Final Answer:

   To execute code before a row is updated, allowing data validation or modification. -> Option A

4. Quick Check:

   BEFORE UPDATE triggers run before update [OK]

Hint: BEFORE UPDATE triggers run before saving changes [OK]

Common Mistakes:

• Confusing BEFORE UPDATE with AFTER UPDATE triggers
• Thinking triggers prevent updates entirely
• Assuming triggers create backups automatically

2. Which of the following is the correct syntax to create a BEFORE UPDATE trigger on a table named employees?

easy

A. CREATE TRIGGER before_update_employees BEFORE UPDATE ON employees BEGIN SET NEW.salary = NEW.salary * 1.1; END;

B. CREATE TRIGGER before_update_employees AFTER UPDATE ON employees FOR EACH ROW BEGIN SET NEW.salary = NEW.salary * 1.1; END;

C. CREATE TRIGGER before_update_employees BEFORE UPDATE employees FOR EACH ROW BEGIN SET NEW.salary = NEW.salary * 1.1; END;

D. CREATE TRIGGER before_update_employees BEFORE UPDATE ON employees FOR EACH ROW BEGIN SET NEW.salary = NEW.salary * 1.1; END;

Solution

1. Step 1: Check trigger timing and table

   The syntax requires BEFORE UPDATE ON employees to specify timing and table.

2. Step 2: Confirm trigger body and FOR EACH ROW

   The trigger must include FOR EACH ROW and a BEGIN...END block for multiple statements.

3. Final Answer:

   CREATE TRIGGER before_update_employees BEFORE UPDATE ON employees FOR EACH ROW BEGIN SET NEW.salary = NEW.salary * 1.1; END; -> Option D

4. Quick Check:

   Correct syntax includes BEFORE UPDATE ON and FOR EACH ROW [OK]

Hint: Use BEFORE UPDATE ON table FOR EACH ROW syntax [OK]

Common Mistakes:

• Using AFTER UPDATE instead of BEFORE UPDATE
• Omitting ON keyword before table name
• Missing FOR EACH ROW clause

3. Given this trigger and table:

CREATE TABLE products (id INT, price DECIMAL(5,2));
CREATE TRIGGER price_check BEFORE UPDATE ON products FOR EACH ROW BEGIN
  IF NEW.price < 0 THEN SET NEW.price = 0; END IF;
END;

What happens if you run:

UPDATE products SET price = -10 WHERE id = 1;

medium

A. The price is set to -10 because triggers don't change values.

B. The update fails with a syntax error.

C. The price is set to 0 because the trigger changes negative prices to zero.

D. The price remains unchanged because the trigger runs after update.

Solution

1. Step 1: Understand trigger logic

   The trigger checks if the new price is less than 0, then sets it to 0.

2. Step 2: Apply update and trigger effect

   Updating price to -10 triggers the BEFORE UPDATE trigger, which changes it to 0 before saving.

3. Final Answer:

   The price is set to 0 because the trigger changes negative prices to zero. -> Option C

4. Quick Check:

   BEFORE UPDATE can modify NEW values [OK]

Hint: BEFORE UPDATE can fix invalid data before saving [OK]

Common Mistakes:

• Thinking triggers run after update
• Assuming negative price is saved as is
• Expecting syntax error from trigger

4. You wrote this trigger:

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

But when updating rows, the status does not change. What is the likely problem?

medium

A. The trigger syntax is missing a delimiter change, causing a syntax error.

B. You cannot assign values to NEW in BEFORE UPDATE triggers.

C. The trigger must be AFTER UPDATE to modify data.

D. The table 'orders' does not have a 'status' column.

Solution

1. Step 1: Check trigger syntax requirements

   MySQL requires changing the delimiter when creating triggers with BEGIN...END blocks.

2. Step 2: Understand effect of missing delimiter

   Without changing delimiter, the trigger creation fails silently or is incomplete, so it doesn't run.

3. Final Answer:

   The trigger syntax is missing a delimiter change, causing a syntax error. -> Option A

4. Quick Check:

   Use DELIMITER to create triggers with BEGIN...END [OK]

Hint: Remember to change delimiter when creating triggers with BEGIN...END [OK]

Common Mistakes:

• Not changing delimiter before CREATE TRIGGER
• Thinking NEW cannot be assigned in BEFORE UPDATE
• Assuming AFTER UPDATE is needed to modify NEW

5. You want to prevent users from lowering their account_balance in the users table. Which BEFORE UPDATE trigger code correctly enforces this rule?

hard

A. CREATE TRIGGER check_balance BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.account_balance < 0 THEN SET NEW.account_balance = 0; END IF; END;

B. CREATE TRIGGER check_balance BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.account_balance < OLD.account_balance THEN SET NEW.account_balance = OLD.account_balance; END IF; END;

C. CREATE TRIGGER check_balance AFTER UPDATE ON users FOR EACH ROW BEGIN IF NEW.account_balance < OLD.account_balance THEN SET NEW.account_balance = OLD.account_balance; END IF; END;

D. CREATE TRIGGER check_balance BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.account_balance > OLD.account_balance THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot increase balance'; END IF; END;

Solution

1. Step 1: Understand the requirement

   The trigger must prevent lowering the account_balance by resetting it to the old value if decreased.

2. Step 2: Analyze options

   CREATE TRIGGER check_balance BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.account_balance < OLD.account_balance THEN SET NEW.account_balance = OLD.account_balance; END IF; END; resets NEW.account_balance to OLD.account_balance if it is less, correctly preventing decrease.

3. Step 3: Check other options

   The code that signals an error prevents increases instead of decreases. AFTER UPDATE triggers cannot modify NEW values. The code that checks if balance is negative only prevents negative values but not decreases to lower positive values.

4. Final Answer:

   CREATE TRIGGER check_balance BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.account_balance < OLD.account_balance THEN SET NEW.account_balance = OLD.account_balance; END IF; END; -> Option B

5. Quick Check:

   Use BEFORE UPDATE and compare NEW vs OLD to prevent decrease [OK]

Hint: Compare NEW and OLD values in BEFORE UPDATE to enforce rules [OK]

Common Mistakes:

• Using AFTER UPDATE to modify NEW values
• Signaling error for wrong condition
• Only checking for negative values, not decreases