MySQL BEFORE DELETE Trigger

Overview

A BEFORE DELETE trigger in MySQL is a special type of stored procedure that automatically runs before a DELETE operation is executed on a table. This allows you to perform actions such as logging the deletion or preventing it based on certain conditions.

Key Concepts

• Trigger: A database object that is automatically executed or fired when certain events occur.
• BEFORE DELETE: This specific trigger is executed before a row is deleted from a table.
• Syntax: A typical trigger is defined using the CREATE TRIGGER statement.

Benefits of Using BEFORE DELETE Triggers

• Data Integrity: Ensures that certain conditions are met before a deletion occurs.
• Logging Changes: Keeps a record of deletions for auditing purposes.
• Preventing Deletions: Can be used to cancel a delete operation based on specific logic.

Example of a BEFORE DELETE Trigger

Here’s a simple example to illustrate how a BEFORE DELETE trigger works:

Scenario

You have a table called employees and you want to keep a log of deleted employees.

Step 1: Create a Log Table

First, create a log table to store deleted records.

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

Step 2: Create the Trigger

Next, create the BEFORE DELETE trigger.

CREATE TRIGGER log_deleted_employees
BEFORE 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 is about to be deleted.
• NOW(): Captures the current date and time when the deletion occurs.

Conclusion

Using a BEFORE DELETE trigger allows developers to manage data more effectively by logging deletions and maintaining data integrity. This is particularly useful in applications where tracking changes is critical.