MySQL AFTER DELETE Trigger

Overview

An AFTER DELETE Trigger in MySQL is a type of database trigger that automatically executes a specific action after a row is deleted from a table. This feature is crucial for maintaining data integrity and performing automated tasks in response to data changes.

Key Concepts

• Trigger: A set of instructions that are automatically executed in response to specific events on a particular table.
• AFTER DELETE: This specifies that the trigger will run after a DELETE operation occurs on the table.
• Table: The database structure from which rows are deleted.

Purpose of AFTER DELETE Trigger

• To log deletions for auditing purposes.
• To enforce business rules or constraints.
• To maintain related data in other tables (e.g., cascading deletes).

Syntax

The basic syntax for creating an AFTER DELETE trigger is:

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

Example

Scenario

Suppose we have a table called employees and we want to keep a log of deleted employees in another table called deleted_employees.

Step 1: Create the deleted_employees Table

CREATE TABLE deleted_employees (
    id INT,
    name VARCHAR(100),
    deleted_at DATETIME
);

Step 2: Create the AFTER DELETE Trigger

CREATE TRIGGER log_deleted_employee
AFTER DELETE
ON employees
FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees (id, name, deleted_at)
    VALUES (OLD.id, OLD.name, NOW());
END;

Explanation

• OLD: Refers to the row that was deleted. In this trigger, the id and name of the deleted employee are captured.
• NOW(): This function gets the current date and time, which is recorded in the deleted_at column.

Conclusion

AFTER DELETE triggers are powerful tools in MySQL for automating tasks that should occur after data is deleted. They help maintain data integrity and assist in auditing changes, making them an important feature for database management.