MySQL BEFORE DELETE Trigger

Introduction

In this chapter, we will learn about the BEFORE DELETE trigger in MySQL. A BEFORE DELETE trigger is a type of trigger that is executed automatically before a DELETE operation is performed on a table. This type of trigger is useful for tasks such as validating conditions before deleting data, archiving records, or maintaining data integrity. We will cover the syntax for creating BEFORE DELETE triggers, examples of their usage, and important considerations for using them in MySQL.

Syntax

The basic syntax for creating a BEFORE DELETE trigger in MySQL is:

CREATE TRIGGER trigger_name
BEFORE DELETE ON table_name
FOR EACH ROW
BEGIN
    -- Trigger body: SQL statements
END;

• trigger_name: The name of the trigger.
• table_name: The name of the table on which the trigger is defined.

Example

Here is an example of how to create a BEFORE DELETE trigger that archives records before they are deleted from the employees table:

1. Create a Database

CREATE DATABASE company;

2. Select the Database

USE company;

3. Create the Employees and Archive Tables

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

CREATE TABLE employee_archive (
    archive_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2),
    deleted_at DATETIME
);

4. Create the BEFORE DELETE Trigger

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_archive (employee_id, first_name, last_name, hire_date, salary, deleted_at)
    VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, OLD.hire_date, OLD.salary, NOW());
END;

In this example, the before_employee_delete trigger archives the employee record into the employee_archive table before the delete operation occurs.

Full Example

Let’s go through a complete example where we create a database, table, and trigger, and then demonstrate the usage of the BEFORE DELETE trigger.

1. Create a Database

CREATE DATABASE company;

2. Select the Database

USE company;

3. Create the Employees and Archive Tables

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

CREATE TABLE employee_archive (
    archive_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2),
    deleted_at DATETIME
);

4. Create the BEFORE DELETE Trigger

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_archive (employee_id, first_name, last_name, hire_date, salary, deleted_at)
    VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, OLD.hire_date, OLD.salary, NOW());
END;

5. Insert Data into the Employees Table

INSERT INTO employees (first_name, last_name, hire_date, salary) VALUES
('Rahul', 'Sharma', '2023-07-05', 50000.00),
('Priya', 'Singh', '2023-07-05', 60000.00),
('Amit', 'Kumar', '2023-07-05', 55000.00);

6. Delete an Employee Record

DELETE FROM employees WHERE employee_id = 2;

7. Query the Employee Archive Table

SELECT * FROM employee_archive;

Output

In this example, the before_employee_delete trigger archives the employee record into the employee_archive table before it is deleted from the employees table.

Using BEFORE DELETE Trigger for Data Validation

You can also use a BEFORE DELETE trigger to enforce data validation rules before deleting a record.

Example: Prevent Deletion of Employees with Active Projects

Assuming there is a projects table that links employees to their active projects:

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100),
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM projects WHERE employee_id = OLD.employee_id) > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete employee with active projects';
    END IF;
END;

In this example, the trigger prevents the deletion of an employee record if the employee has active projects.

Important Considerations

• Performance: Triggers can affect performance, especially if they contain complex logic or if they fire frequently. Use them judiciously.
• Error Handling: Use the SIGNAL statement to raise custom errors within triggers for data validation and business rule enforcement.
• Testing: Thoroughly test triggers to ensure they perform as expected and do not introduce unwanted side effects.
• Data Integrity: Triggers can help maintain data integrity by ensuring certain conditions are met before allowing operations like deletes.

Conclusion

The BEFORE DELETE trigger in MySQL is used for automating tasks and enforcing business rules before data is deleted from a table. This chapter covered the syntax for creating BEFORE DELETE triggers, provided examples of their usage, and discussed important considerations.