MySQL Delete Triggers Tutorial

What Are MySQL Delete Triggers?

A delete trigger in MySQL is a stored program that runs automatically when a DELETE statement affects a table.

You can define triggers to execute either before the deletion happens (BEFORE DELETE) or after the deletion completes (AFTER DELETE).

• BEFORE DELETE triggers can prevent deletion or modify data before removal.
• AFTER DELETE triggers are useful for logging or cascading actions after data is deleted.

Syntax for Creating Delete Triggers

The basic syntax to create a delete trigger includes specifying the trigger timing, event, and the table it applies to.

You write the trigger body using SQL statements that execute when the trigger fires.

• CREATE TRIGGER trigger_name BEFORE|AFTER DELETE ON table_name
• FOR EACH ROW BEGIN ... END;

Example: Logging Deleted Rows

Let's create a trigger that logs information about deleted rows into an audit table.

This example uses an AFTER DELETE trigger to record the deleted row's ID and deletion time.

Use Cases for Delete Triggers

Delete triggers are useful in many scenarios where automatic actions are needed when data is removed.

• Maintaining audit trails of deleted records.
• Enforcing complex business rules before deletion.
• Cascading deletions or updates in related tables.
• Preventing deletion under certain conditions.