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.