Create a MySQL Trigger for Logging Salary Updates

Create a Trigger to Log Salary Changes

Write a MySQL query to create a trigger that logs salary changes in the Employees table to a SalaryLog table.

Solution:

-- Change the delimiter to allow multi-line trigger definition
DELIMITER //

-- Create a trigger named 'LogSalaryChange'
CREATE TRIGGER LogSalaryChange  -- Trigger name
AFTER UPDATE ON Employees       -- Trigger fires after an update on the Employees table
FOR EACH ROW                    -- The trigger will execute for each row affected by the update
BEGIN
    -- Insert the old and new salary into the SalaryLog table
    INSERT INTO SalaryLog (EmployeeID, OldSalary, NewSalary, ChangeDate)  -- Insert into SalaryLog table
    VALUES (OLD.EmployeeID, OLD.Salary, NEW.Salary, NOW());  -- Use OLD and NEW values to capture the old and new salary and the current timestamp
END //
-- Reset the delimiter back to ';'
DELIMITER ;

Explanation:

• Purpose of the Query:
• The goal is to automate logging of salary changes using a trigger.
• Key Components:
• CREATE TRIGGER: Defines the trigger.
• AFTER UPDATE: Specifies when the trigger should execute.
• OLD and NEW: Access the old and new values of the updated row.
• Why use Triggers?:
• Triggers automate actions based on database events, ensuring data integrity.
• Real-World Application:
• For example, in a payroll system, you might use a trigger to track salary changes.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a trigger that logs changes to a department's budget in the Departments table.
• Write a MySQL query to create a trigger that logs changes to a project's deadline in the Projects table.
• Write a MySQL query to create a trigger that logs changes to a customer's address in the Customers table.
• Write a MySQL query to create a trigger that logs changes to an order's status in the Orders table.

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

Previous MySQL Exercise: Call the Stored Procedure to Delete an Employee.
Next MySQL Exercise: Create a Trigger to Prevent Invalid Salary Updates.