DELETE triggers in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of DELETE trigger with BEFORE DELETE

Consider a table employees with columns id and salary. A BEFORE DELETE trigger reduces the salary by 10% before the row is deleted. What will be the salary value logged if the row with id=3 and salary=1000 is deleted?

MySQL

CREATE TRIGGER before_delete_employee
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  SET @old_salary = OLD.salary;
  SET NEW.salary = OLD.salary * 0.9;
  INSERT INTO salary_log(employee_id, old_salary, new_salary) VALUES (OLD.id, @old_salary, NEW.salary);
END;

DELETE FROM employees WHERE id = 3;

A NULL

B 900

C Error: Cannot assign NEW in BEFORE DELETE trigger

D 1000

💡 Hint

In MySQL, the NEW keyword cannot be used in DELETE triggers.

In MySQL, DELETE triggers can only access OLD values. The NEW keyword is not allowed in DELETE triggers, so trying to assign NEW.salary causes an error.

❓ query_result

intermediate

Effect of AFTER DELETE trigger on audit table

Given a table orders and an audit table orders_audit, an AFTER DELETE trigger inserts the deleted row's id and deletion time into orders_audit. What will be the content of orders_audit after deleting the order with id=5?

MySQL

CREATE TRIGGER after_delete_order
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO orders_audit(order_id, deleted_at) VALUES (OLD.id, NOW());
END;

DELETE FROM orders WHERE id = 5;

A Error: OLD keyword not allowed in AFTER DELETE

B No rows inserted

C A new row with order_id=NULL and current timestamp

D A new row with order_id=5 and current timestamp

💡 Hint

AFTER DELETE triggers can access OLD values to log deleted data.

The AFTER DELETE trigger runs after the row is deleted and can access OLD values. It inserts a new row into orders_audit with the deleted order's id and the current timestamp.

📝 Syntax

advanced

Identify the syntax error in DELETE trigger

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

MySQL

CREATE TRIGGER trg_delete_customer
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO deleted_customers(id, deleted_on) VALUES (OLD.id, NOW());
END;

A Missing delimiter change before trigger creation

B FOR EACH ROW should be FOR EACH STATEMENT

C OLD keyword cannot be used in DELETE triggers

D Trigger name cannot contain underscores

💡 Hint

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

In MySQL, when creating triggers with BEGIN...END blocks, you must change the delimiter temporarily to avoid syntax errors. Not doing so causes a syntax error.

❓ optimization

advanced

Optimizing DELETE trigger to avoid performance issues

A DELETE trigger on a large products table logs each deleted product to products_log. Which option best improves performance?

A Insert deleted rows in batches outside the trigger

B Disable the trigger during bulk deletes

C Add indexes on products_log for faster inserts

D Use AFTER DELETE trigger instead of BEFORE DELETE

💡 Hint

Triggers run per row and can slow down bulk operations.

Inserting deleted rows in batches outside the trigger reduces overhead and improves performance during bulk deletes.

🧠 Conceptual

expert

Understanding cascading DELETE triggers and recursion

In a MySQL database, table A has a DELETE trigger that deletes related rows in table B. Table B has a DELETE trigger that deletes related rows in table C. What happens if a DELETE on A causes cascading deletes in B and C?

A Triggers cause infinite recursion and crash the server

B All triggers execute in order without recursion issues

C Only the first trigger executes; others are ignored

D MySQL prevents cascading triggers automatically

💡 Hint

MySQL supports cascading triggers but limits recursion depth.

MySQL executes cascading DELETE triggers in order. It prevents infinite recursion by limiting trigger nesting depth (default 16). Proper design avoids recursion issues.