DELETE triggers in MySQL - Step-by-Step Execution

Concept Flow - DELETE triggers

DELETE command issued

↓

Check for DELETE triggers

↓

Trigger BEFORE DELETE?

Yes→Execute BEFORE DELETE trigger

↓

Continue deletion

↓

Delete row from table

↓

Trigger AFTER DELETE?

Yes→Execute AFTER DELETE trigger

↓

Row removed from table

When a DELETE command runs, MySQL checks for any DELETE triggers. It runs BEFORE DELETE triggers first, then deletes the row, then runs AFTER DELETE triggers.

Execution Sample

MySQL

CREATE TRIGGER trg_before_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log VALUES (OLD.id, 'deleted');
END;

This trigger logs the id of an employee before that employee's row is deleted.

Execution Table

Step	Action	Trigger Type	Row Data	Effect
1	DELETE issued for employee id=5	None	id=5, name='John'	Start delete process
2	Check for BEFORE DELETE trigger	BEFORE DELETE	id=5, name='John'	Trigger found, execute trigger
3	Execute BEFORE DELETE trigger	BEFORE DELETE	id=5, name='John'	Insert audit log with id=5
4	Delete row from employees	None	id=5, name='John'	Row removed from employees table
5	Check for AFTER DELETE trigger	AFTER DELETE	id=5, name='John'	No AFTER DELETE trigger found
6	End of DELETE operation	None	None	Delete completed

💡 Delete operation ends after executing triggers and removing the row

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Row to delete	id=5, name='John'	id=5, name='John'	id=5, name='John'	Deleted	Deleted
Audit log entries	empty	empty	contains id=5 deletion record	contains id=5 deletion record	contains id=5 deletion record

Key Moments - 2 Insights

Why does the BEFORE DELETE trigger run before the row is deleted?

The BEFORE DELETE trigger runs first to allow actions like logging or validation before the actual deletion happens, as shown in execution_table step 3.

What happens if there is no AFTER DELETE trigger?

If no AFTER DELETE trigger exists, MySQL simply skips that step and finishes the delete operation, as seen in execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of the row after step 4?

A Row still exists in the table

B Row is updated but not deleted

C Row is deleted from the table

D Row is duplicated

💡 Hint

Check the 'Effect' column at step 4 in the execution_table

At which step is the audit log updated?

A Step 3

B Step 2

C Step 4

D Step 5

💡 Hint

Look for the step where the BEFORE DELETE trigger executes in the execution_table

If an AFTER DELETE trigger was added, when would it run?

A Before the row is deleted

B After the row is deleted

C At the same time as BEFORE DELETE trigger

D Only if the BEFORE DELETE trigger fails

💡 Hint

Refer to the concept_flow diagram and execution_table step 5

Concept Snapshot

DELETE triggers run automatically when a DELETE command affects a table.
BEFORE DELETE triggers run before the row is removed.
AFTER DELETE triggers run after the row is removed.
Use triggers to log, validate, or modify related data during deletion.
Triggers execute for each row affected by the DELETE.
If no triggers exist, DELETE simply removes the row.

Full Transcript

When you issue a DELETE command in MySQL, the database checks if there are any DELETE triggers on the table. If a BEFORE DELETE trigger exists, it runs first before the row is deleted. This allows you to do things like log the deletion or check conditions. Then the row is removed from the table. After that, if there is an AFTER DELETE trigger, it runs to perform any follow-up actions. If no triggers exist, the row is simply deleted. This process happens for each row affected by the DELETE command.

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