MySQL AFTER DELETE Trigger

Introduction

A MySQL AFTER DELETE trigger is a type of stored program that automatically executes after a DELETE operation is performed on a table. This trigger is useful for maintaining audit logs, enforcing business rules, or synchronizing data across tables.

Syntax

CREATE TRIGGER trigger_name  
AFTER DELETE  
ON table_name  
FOR EACH ROW  
BEGIN  
   -- Trigger logic here
END;

Explanation:

CREATE TRIGGER trigger_name → Defines the name of the trigger.
AFTER DELETE → Specifies that the trigger executes after a row is deleted.
ON table_name → Specifies the table on which the trigger is created.
FOR EACH ROW → Ensures the trigger runs once for each deleted row.
BEGIN ... END; → Contains the logic to be executed after deletion.

Example 1: Creating an AFTER DELETE Trigger for Logging

Let's say we have a customers table and an audit_log table. Whenever a row is deleted from customers, the deleted record is logged into audit_log.

Step 1: Create the Customers Table

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

Step 2: Create the Audit Log Table

CREATE TABLE audit_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    details TEXT
);

Step 3: Create the AFTER DELETE Trigger

DELIMITER //

CREATE TRIGGER after_customer_delete  
AFTER DELETE  
ON customers  
FOR EACH ROW  
BEGIN  
    INSERT INTO audit_log (customer_id, details)  
    VALUES (OLD.id, CONCAT('Customer ', OLD.name, ' with email ', OLD.email, ' was deleted.'));
END;  

//  
DELIMITER ;

Explanation:

OLD.id, OLD.name, OLD.email → Captures the deleted row’s values before removal.
INSERT INTO audit_log → Logs the deleted customer's details.

Example 2: Preventing Deletion from a Critical Table

If you want to restrict deletions on a table and notify users, use a trigger with SIGNAL SQLSTATE.

DELIMITER //

CREATE TRIGGER prevent_employee_delete  
AFTER DELETE  
ON employees  
FOR EACH ROW  
BEGIN  
    SIGNAL SQLSTATE '45000'  
    SET MESSAGE_TEXT = 'Deleting employees is not allowed!';
END;  

//  
DELIMITER ;

Explanation:

The trigger prevents deletion by raising an error (45000).
If someone tries to delete from employees, MySQL will abort the operation and show the error message.

Testing the AFTER DELETE Trigger

Insert Sample Data

INSERT INTO customers (name, email) VALUES ('John Doe', 'john@example.com');

Delete a Record

DELETE FROM customers WHERE id = 1;

Check the Audit Log

SELECT * FROM audit_log;

Expected Output:

+--------+-------------+---------------------+-------------------------------------------+
| log_id | customer_id | deleted_at          | details                                   |
+--------+-------------+---------------------+-------------------------------------------+
| 1      | 1           | 2025-01-31 10:00:00 | Customer John Doe with email john@example.com was deleted. |
+--------+-------------+---------------------+-------------------------------------------+

Dropping an AFTER DELETE Trigger

To remove a trigger, use:

DROP TRIGGER IF EXISTS after_customer_delete;

Conclusion

AFTER DELETE triggers execute after a DELETE operation.
Useful for logging deletions, enforcing constraints, or synchronizing data.
OLD keyword allows access to the deleted row’s data.
Triggers run automatically and help maintain data integrity.

Would you like a more advanced example, such as cascading deletes with triggers? 🚀

Souy Soeng

MySQL AFTER DELETE Trigger