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.

Practice

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

easy

A. To automatically run code when a row is deleted

B. To insert new rows into a table

C. To update rows before selecting them

D. To create new tables dynamically

Solution

1. Step 1: Understand what triggers do

   Triggers run automatically when certain actions happen on a table, like DELETE.

2. Step 2: Identify the purpose of DELETE triggers

   DELETE triggers specifically run when rows are deleted to perform extra tasks.

3. Final Answer:

   To automatically run code when a row is deleted -> Option A

4. Quick Check:

   DELETE triggers = automatic code on row deletion [OK]

Hint: Triggers run automatically on data changes like DELETE [OK]

Common Mistakes:

• Confusing DELETE triggers with INSERT or UPDATE triggers
• Thinking triggers create or modify tables
• Believing triggers run only manually

2. Which of the following is the correct syntax to create a BEFORE DELETE trigger in MySQL?

easy

A. CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END;

B. CREATE TRIGGER trg AFTER DELETE ON table_name FOR EACH ROW BEGIN ... END;

C. CREATE TRIGGER trg BEFORE UPDATE ON table_name FOR EACH ROW BEGIN ... END;

D. CREATE TRIGGER trg BEFORE DELETE FROM table_name BEGIN ... END;

Solution

1. Step 1: Recall the correct trigger syntax

   MySQL triggers use CREATE TRIGGER name BEFORE|AFTER event ON table FOR EACH ROW BEGIN ... END;

2. Step 2: Match syntax for BEFORE DELETE

   CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END; matches this exactly with BEFORE DELETE and correct structure.

3. Final Answer:

   CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END; -> Option A

4. Quick Check:

   Correct syntax = CREATE TRIGGER trg BEFORE DELETE ON table_name FOR EACH ROW BEGIN ... END; [OK]

Hint: Syntax: CREATE TRIGGER name BEFORE|AFTER DELETE ON table [OK]

Common Mistakes:

• Using AFTER instead of BEFORE when asked
• Wrong event like UPDATE instead of DELETE
• Missing FOR EACH ROW clause

3. Given this trigger and table:

CREATE TABLE users (id INT, name VARCHAR(20));
CREATE TRIGGER log_delete AFTER DELETE ON users FOR EACH ROW BEGIN
INSERT INTO deleted_log VALUES (OLD.id, OLD.name);
END;

What happens when DELETE FROM users WHERE id=1; is executed?

medium

A. The row with id=1 is deleted but nothing is logged

B. The row with id=1 is deleted and its data is inserted into deleted_log

C. The DELETE statement fails with a syntax error

D. The row with id=1 is not deleted because of the trigger

Solution

1. Step 1: Understand AFTER DELETE trigger behavior

   The trigger runs after a row is deleted and inserts OLD row data into deleted_log.

2. Step 2: Analyze the DELETE command effect

   Deleting id=1 removes that row and the trigger logs its data into deleted_log.

3. Final Answer:

   The row with id=1 is deleted and its data is inserted into deleted_log -> Option B

4. Quick Check:

   AFTER DELETE trigger logs deleted row [OK]

Hint: AFTER DELETE triggers run after row removal to log data [OK]

Common Mistakes:

• Thinking trigger prevents deletion
• Assuming no logging happens
• Confusing OLD with NEW in DELETE triggers

4. Identify the error in this trigger definition:

CREATE TRIGGER trg BEFORE DELETE ON users
BEGIN
INSERT INTO deleted_log VALUES (OLD.id, OLD.name);
END;

medium

A. Using NEW in a DELETE trigger is invalid

B. BEFORE DELETE triggers cannot modify data

C. Trigger body must be a single statement without BEGIN...END

D. Missing FOR EACH ROW clause

Solution

1. Step 1: Check trigger syntax requirements

   MySQL requires FOR EACH ROW clause in row-level triggers.

2. Step 2: Identify missing FOR EACH ROW

   The trigger lacks FOR EACH ROW, causing syntax error.

3. Final Answer:

   Missing FOR EACH ROW clause -> Option D

4. Quick Check:

   FOR EACH ROW is mandatory in triggers [OK]

Hint: Always include FOR EACH ROW in row-level triggers [OK]

Common Mistakes:

• Using NEW in DELETE triggers (allowed only OLD)
• Omitting FOR EACH ROW clause
• Incorrect BEGIN...END usage

5. You want to prevent deletion of rows in the orders table if the order status is 'shipped'. Which DELETE trigger logic is correct?

hard

A. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF NEW.status = 'shipped' THEN DELETE FROM orders WHERE id = OLD.id; END IF; END;

B. CREATE TRIGGER trg AFTER DELETE ON orders FOR EACH ROW BEGIN IF NEW.status = 'shipped' THEN ROLLBACK; END IF; END;

C. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END;

D. CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN UPDATE orders SET status = 'cancelled' WHERE id = OLD.id; END IF; END;

Solution

1. Step 1: Understand how to prevent deletion

   Use BEFORE DELETE trigger and SIGNAL to raise an error and stop deletion.

2. Step 2: Analyze each option's logic

   CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END; correctly uses BEFORE DELETE, checks OLD.status, and signals error to prevent deletion.

3. Final Answer:

   CREATE TRIGGER trg BEFORE DELETE ON orders FOR EACH ROW BEGIN IF OLD.status = 'shipped' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete shipped orders'; END IF; END; -> Option C

4. Quick Check:

   Use SIGNAL in BEFORE DELETE to block deletion [OK]

Hint: Use SIGNAL in BEFORE DELETE to stop unwanted deletions [OK]

Common Mistakes:

• Using AFTER DELETE to prevent deletion (too late)
• Using NEW in DELETE triggers (only OLD is valid)
• Trying to delete inside a DELETE trigger causing recursion