Track Deleted Employees Using a MySQL Trigger

Create a Trigger to Log Employee Deletions

Write a MySQL query to create a trigger that logs deleted employees to a DeletedEmployeesLog table.

Solution:

-- Create a trigger named `LogDeletedEmployees`
-- This trigger will log deleted employees into the DeletedEmployeesLog table
DELIMITER //

-- Define the trigger `LogDeletedEmployees` that will fire after a delete operation on the Employees table
CREATE TRIGGER LogDeletedEmployees
AFTER DELETE ON Employees
FOR EACH ROW
BEGIN
    -- Insert the deleted employee's details into the DeletedEmployeesLog table
    -- The details include EmployeeID, Name, DepartmentID, Salary, and DeletionDate (current timestamp)
    INSERT INTO DeletedEmployeesLog (EmployeeID, Name, DepartmentID, Salary, DeletionDate)
    VALUES (OLD.EmployeeID, OLD.Name, OLD.DepartmentID, OLD.Salary, NOW());
END //

-- Reset the delimiter to the default `;`
DELIMITER ;

Explanation:

• Purpose of the Query:
• The goal is to automate logging of deleted employees using a trigger.
• Key Components:
• AFTER DELETE: Specifies when the trigger should execute.
• OLD: Accesses the values of the deleted row.
• Why use Triggers?:
• Triggers automate actions based on database events, ensuring data integrity.
• Real-World Application:
• For example, in a HR system, you might use a trigger to track deleted employees.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a trigger that logs deleted customers to a DeletedCustomersLog table.
• Write a MySQL query to create a trigger that logs deleted orders to a DeletedOrdersLog table.
• Write a MySQL query to create a trigger that logs deleted products to a DeletedProductsLog table.
• Write a MySQL query to create a trigger that logs deleted projects to a DeletedProjectsLog table.

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

Previous MySQL Exercise: Call the Stored Procedure to Archive Old Employees.