Using ‘AFTER DELETE’ trigger in MySQL 8: Explained with examples

Introduction

MySQL triggers are database callbacks that execute automatically in response to certain events on a particular table or view in the database. One such trigger is the ‘AFTER DELETE’ trigger, which is invoked after a DELETE operation is performed on a table. These triggers can help maintain data integrity, perform automatic clean-up, or audit the changes in the database. This step-by-step tutorial will guide you through the process of using the ‘AFTER DELETE’ trigger in MySQL 8, backed by practical examples to help you understand the concept more effectively.

Understanding MySQL Triggers

The ‘AFTER DELETE’ trigger is one of the several types of triggers supported by MySQL, including BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, and AFTER UPDATE. An ‘AFTER DELETE’ trigger is beneficial for operations that you want to occur only after a successful deletion from the database table. A classic example would be maintaining an audit log that records deleted entries or cascading custom deletions to other related tables to maintain referential integrity.

Setting Up the Environment for Examples

Before diving into the examples, make sure you have access to a MySQL 8 database. Install MySQL server on your machine if it’s not already available. Once installed, you can access it through the command line or any GUI tools like PHPMyAdmin, MySQL Workbench, etc. For these examples, I’ll assume you’re using the command line.

To begin, you’ll need to create a simple database and a table to work with:

CREATE DATABASE IF NOT EXISTS exampleDB;
USE exampleDB;

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees (name, position, salary)
VALUES ('John Doe', 'Software Engineer', 70000.00),
       ('Jane Roe', 'Project Manager', 90000.00);

Creating a Basic ‘AFTER DELETE’ Trigger

The following example shows how to create a simple ‘AFTER DELETE’ trigger. This trigger will automatically store the details of the deleted employee into another table called ‘deleted_employees’ for audit purposes:

CREATE TABLE deleted_employees (
    id INT,
    name VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10,2),
    deletion_date TIMESTAMP
);

DELIMITER $
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees VALUES (OLD.id, OLD.name, OLD.position, OLD.salary, NOW());
END $
DELIMITER ;

In the above code snippet, the DELIMITER statement changes the default command ender from ‘;’ to ‘$$’. This allows the entire trigger syntax to be treated as a single command, avoiding errors that can happen when using default semicolon delimiters inside trigger definitions. After the trigger has been created, the normal delimiter ‘;’ is restored by resetting DELIMITER back to ‘;’.

Trigger Execution Example

Now that the trigger is set up, let’s see it in action by deleting a record from the ’employees’ table and examining the result in the ‘deleted_employees’ table:

DELETE FROM employees WHERE name = 'Jane Roe';

SELECT * FROM deleted_employees;

After running the SELECT statement, you should see that the record of ‘Jane Roe’ is now stored in the ‘deleted_employees’ table with a ‘deletion_date’ timestamp, thereby confirming that our ‘AFTER DELETE’ trigger works as intended.

Handling Complex Deletions

Sometimes, you’ll encounter scenarios where deleting an entry from one table requires more complex operations on related data. For example, let’s imagine we have a table called ‘project_assignments’ where employees are assigned to various projects. We want to remove an employee’s assignments upon their deletion.

CREATE TABLE project_assignments (
    employee_id INT,
    project_name VARCHAR(100),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

DELIMITER $
CREATE TRIGGER after_employee_delete_complex
AFTER DELETE ON employees FOR EACH ROW
BEGIN
    -- Delete all project assignments for the deleted employee
    DELETE FROM project_assignments WHERE employee_id = OLD.id;
    -- Insert into deleted employees audit table
    INSERT INTO deleted_employees VALUES (OLD.id, OLD.name, OLD.position, OLD.salary, NOW());
END $
DELIMITER ;

This revised trigger performs two operations: it deletes all entries related to the employee in the ‘project_assignments’ table and then works similarly to our previous audit log example by inserting an entry into the ‘deleted_employees’ table.

Validating Cascading Deletions

The practical application of this trigger is when an employee who is part of various projects is deleted:

DELETE FROM employees WHERE id = 1;

SELECT * FROM project_assignments;

After the DELETE statement, you should find that all assignments associated with the employee with ID 1 have been removed from the ‘project_assignments’ table, confirming that cascading deletions are executed via the trigger.

Advanced Techniques

For advanced usage, ‘AFTER DELETE’ triggers can be used in combination with other database features. For example, you might use stored procedures within a trigger for more encapsulated and reusable code, or combine EVENT schedulers to perform periodic purging in tandem with trigger-based auditing for detailed data management.

Here’s a MySQL example that demonstrates the use of an ‘AFTER DELETE’ trigger combined with a stored procedure. This example represents a scenario where you have a table that logs deletions from another table. Additionally, an EVENT scheduler can be set up to periodically purge older records from the log table:

-- Create a table to log deletions
CREATE TABLE deleted_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    original_id INT,
    deleted_at DATETIME
);

-- Create a stored procedure to insert into the log table
DELIMITER //
CREATE PROCEDURE logDeletion(originalId INT)
BEGIN
    INSERT INTO deleted_records (original_id, deleted_at) VALUES (originalId, NOW());
END //
DELIMITER ;

-- Create your main table
CREATE TABLE main_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
);

-- Create an AFTER DELETE trigger on your main table
DELIMITER //
CREATE TRIGGER afterDeleteMainData
AFTER DELETE ON main_data
FOR EACH ROW
BEGIN
    CALL logDeletion(OLD.id);
END //
DELIMITER ;

-- Create an EVENT to periodically purge old records from the log table
DELIMITER //
CREATE EVENT purgeOldRecords
ON SCHEDULE EVERY 1 WEEK
DO
    DELETE FROM deleted_records WHERE deleted_at < NOW() - INTERVAL 1 MONTH;
END //
DELIMITER ;

-- Enable the event scheduler
SET GLOBAL event_scheduler = ON;

Explanation:

• Log Table: deleted_records is used to log deletions from main_data.
• Stored Procedure: logDeletion inserts a record into deleted_records. It’s a reusable piece of code for logging deletions.
• Main Table: main_data is the table from which records will be deleted.
• Trigger: afterDeleteMainData is an AFTER DELETE trigger. When a row is deleted from main_data, it calls logDeletion to log this deletion.
• Event Scheduler: purgeOldRecords is an EVENT that runs every week. It purges records from deleted_records that are older than a month.
• Enabling Event Scheduler: The event scheduler must be enabled to run scheduled events.

Conclusion

In conclusion, the ‘AFTER DELETE’ trigger in MySQL 8 is a powerful tool that automates the process of handling data after a deletion event. By utilizing this feature effectively, developers can ensure the integrity of their database, conform to business logic, and maintain records of actions performed. Triggers are a testament to the flexibility and sophistication possible with modern database management systems.