Trigger best practices and limitations in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

When to avoid using triggers in MySQL?

Which of the following is a recommended reason to avoid using triggers in MySQL?

A When you want to keep business logic simple and maintainable by avoiding hidden side effects

B When you need to enforce data integrity at the database level

C When you want to automatically update audit columns like last_modified

D When you want to replicate data changes to another table automatically

💡 Hint

Think about how triggers can affect code readability and debugging.

Triggers can cause hidden side effects that make debugging and maintenance harder. Keeping business logic explicit in application code is often better.

❓ query_result

intermediate

Output of a BEFORE INSERT trigger modifying data

Given this table and trigger, what will be the value of the 'name' column after inserting ('john')? Table: users(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)) Trigger: CREATE TRIGGER before_insert_users BEFORE INSERT ON users FOR EACH ROW SET NEW.name = UPPER(NEW.name);

MySQL

INSERT INTO users (name) VALUES ('john');
SELECT name FROM users WHERE id = LAST_INSERT_ID();

A 'john'

B Syntax error in trigger

C NULL

D 'JOHN'

💡 Hint

BEFORE INSERT triggers can modify the data before it is saved.

The trigger converts the 'name' to uppercase before inserting, so the stored value is 'JOHN'.

📝 Syntax

advanced

Identify the syntax error in this trigger definition

Which option contains a syntax error in the MySQL trigger definition?

MySQL

CREATE TRIGGER trg_after_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < OLD.salary THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot decrease';
  END IF;
END;

A Missing delimiter change before trigger creation

B FOR EACH ROW should be FOR EACH STATEMENT

C SIGNAL SQLSTATE is not allowed in triggers

D Trigger name cannot contain underscores

💡 Hint

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

MySQL needs the delimiter changed before creating triggers with multiple statements to avoid premature end of the statement.

❓ optimization

advanced

Best practice to avoid performance issues with triggers

Which practice helps avoid performance problems caused by triggers in MySQL?

A Use triggers to replace all application-level validations

B Keep trigger logic simple and avoid complex queries inside triggers

C Create multiple triggers for the same event to split logic

D Use triggers to perform heavy data aggregation on large tables

💡 Hint

Think about how triggers run synchronously during data changes.

Complex or heavy queries inside triggers slow down data modification operations. Keeping triggers simple improves performance.

🔧 Debug

expert

Why does this trigger cause an infinite loop?

Consider this trigger: CREATE TRIGGER trg_after_update AFTER UPDATE ON orders FOR EACH ROW BEGIN UPDATE orders SET status = 'processed' WHERE id = NEW.id; END; Why does this trigger cause an infinite loop?

A Because the trigger syntax is invalid and causes repeated errors

B Because AFTER UPDATE triggers cannot modify the same table

C Because the UPDATE inside the trigger fires the same trigger again repeatedly

D Because the trigger does not have a WHERE clause

💡 Hint

Think about what happens when a trigger updates the same table it listens to.

The UPDATE inside the trigger causes the trigger to fire again, creating an infinite loop of updates.