Prevent Deleting Active Employees using a MySQL Trigger

Create a Trigger to Prevent Deleting Active Employees

Write a MySQL query to create a trigger that prevents deleting employees who are marked as active.

Solution:

-- Set the delimiter to `//` to allow multi-line trigger definition
DELIMITER //

-- Create a trigger named `PreventDeleteActiveEmployees`
CREATE TRIGGER PreventDeleteActiveEmployees
BEFORE DELETE ON Employees  -- Trigger activates before deleting a row from the Employees table
FOR EACH ROW  -- Executes for every row that is being deleted
BEGIN
    -- Check if the employee is active (assuming `IsActive = 1` means active)
    IF OLD.IsActive = 1 THEN
        -- Raise an error and prevent the deletion
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Cannot delete active employees';
    END IF;
END //  -- End of the trigger definition

-- Reset the delimiter to `;` for normal MySQL execution
DELIMITER ; 

Explanation:

• Purpose of the Query:
• The goal is to enforce business rules using a trigger.
• Key Components:
• BEFORE DELETE: Specifies when the trigger should execute.
• SIGNAL SQLSTATE: Raises an error to prevent invalid deletions.
• Why use Triggers?:
• Triggers enforce data validation rules at the database level.
• Real-World Application:
• For example, in a HR system, you might use a trigger to prevent deleting active employees.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a trigger that prevents deleting active customers from the Customers table.
• Write a MySQL query to create a trigger that prevents deleting active orders from the Orders table.
• Write a MySQL query to create a trigger that prevents deleting active projects from the Projects table.
• Write a MySQL query to create a trigger that prevents deleting active departments from the Departments table.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Call the Stored Procedure to Count Employees.
Next MySQL Exercise: Stored Procedure to Calculate Total Salary by Department.