MySQL BEFORE DELETE Trigger

MySQL BEFORE DELETE Trigger

Introduction

The BEFORE DELETE trigger in MySQL is used to execute a set of SQL statements before a row is deleted from a table. This is useful for:

• Logging deleted records.
• Preventing accidental deletions.
• Maintaining referential integrity.

Syntax

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

Parameters:

• trigger_name → Unique name for the trigger.
• table_name → The table where the deletion occurs.
• BEFORE DELETE → Specifies that the trigger executes before a delete operation.
• FOR EACH ROW → The trigger fires for each row affected by the DELETE statement.

Example 1: Logging Deleted Records

Let's create a trigger that saves deleted records in an audit table before they are removed.

Step 1: Create the Main Table

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

Step 2: Create an Audit Table

CREATE TABLE employee_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    deleted_employee_id INT,
    name VARCHAR(100),
    position VARCHAR(50),
    salary DECIMAL(10,2),
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create the BEFORE DELETE Trigger

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (deleted_employee_id, name, position, salary)
    VALUES (OLD.id, OLD.name, OLD.position, OLD.salary);
END;

Step 4: Insert Sample Data

INSERT INTO employees (name, position, salary) VALUES
('Alice', 'Manager', 80000),
('Bob', 'Developer', 60000);

Step 5: Delete a Record

DELETE FROM employees WHERE id = 1;

Step 6: Verify the Audit Table

SELECT * FROM employee_audit;

Output:

+----+-------------------+-------+----------+--------+---------------------+
| id | deleted_employee_id | name  | position | salary | deleted_at          |
+----+-------------------+-------+----------+--------+---------------------+
|  1 |                 1 | Alice | Manager  | 80000  | 2025-01-31 10:00:00 |
+----+-------------------+-------+----------+--------+---------------------+

Example 2: Prevent Deleting a Specific Record

You can prevent the deletion of critical data using a BEFORE DELETE trigger.

CREATE TRIGGER prevent_manager_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.position = 'Manager' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete a Manager!';
    END IF;
END;

Test the Trigger

DELETE FROM employees WHERE position = 'Manager';

Error Output:

ERROR 1644 (45000): Cannot delete a Manager!

Conclusion

• BEFORE DELETE triggers execute before a row is deleted.
• Useful for logging, preventing deletions, and maintaining integrity.
• You can use OLD.column_name to reference the deleted row's values.
• Triggers help enforce business rules automatically.

Would you like more complex trigger examples? 🚀