How to Create an AFTER DELETE Trigger in MySQL

In MySQL, you create an AFTER DELETE trigger using the CREATE TRIGGER statement followed by the trigger name, timing (AFTER DELETE), and the table it applies to. Inside the trigger body, you write SQL statements that run automatically after a row is deleted from the specified table.

Syntax

The basic syntax to create an AFTER DELETE trigger in MySQL is:

• CREATE TRIGGER trigger_name: Names your trigger.
• AFTER DELETE ON table_name: Specifies the trigger runs after a delete on the table.
• FOR EACH ROW: Runs the trigger for every deleted row.
• BEGIN ... END: Contains the SQL statements to execute.

sql

CREATE TRIGGER trigger_name
AFTER DELETE ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements here
END;

Example

This example creates an AFTER DELETE trigger that logs deleted user IDs into a separate table called deleted_users_log.

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE deleted_users_log (
  deleted_id INT,
  deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO deleted_users_log (deleted_id) VALUES (OLD.id);
END$$
DELIMITER ;

-- Example delete to test trigger
DELETE FROM users WHERE id = 1;

Output

Query OK, 0 rows affected (for trigger creation)
Query OK, 1 row affected (for delete)

-- After delete, deleted_users_log will have the deleted user id

Common Pitfalls

• Forgetting to use DELIMITER to change the statement delimiter when creating triggers causes syntax errors.
• Using OLD keyword to access deleted row values inside the trigger is required; NEW is not available in DELETE triggers.
• Triggers cannot modify the same table that fired them to avoid recursion errors.

sql

/* Wrong: Missing DELIMITER change */
CREATE TRIGGER after_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO deleted_users_log (deleted_id) VALUES (OLD.id);
END;

/* Right: Use DELIMITER to define trigger body */
DELIMITER $$
CREATE TRIGGER after_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
  INSERT INTO deleted_users_log (deleted_id) VALUES (OLD.id);
END$$
DELIMITER ;

Quick Reference

Part	Description
CREATE TRIGGER trigger_name	Defines the trigger and its name
AFTER DELETE ON table_name	Runs trigger after a row is deleted from the table
FOR EACH ROW	Executes trigger for every deleted row
BEGIN ... END	Contains SQL statements to run
OLD	References the deleted row's data inside the trigger

Key Takeaways

Use CREATE TRIGGER with AFTER DELETE to run code after row deletions.

Always use DELIMITER to define trigger bodies in MySQL.

Use OLD to access deleted row data inside the trigger.

Triggers run once per deleted row when using FOR EACH ROW.

Avoid modifying the same table inside its own trigger to prevent errors.