Create a MySQL Trigger to Track Last Modified Date

Create a Trigger to Update Last Modified Date

Write a MySQL query to create a trigger that updates the LastModified column in the Employees table whenever a row is updated.

Solution:

-- Change the delimiter to allow defining a multi-line trigger
DELIMITER //

-- Create a trigger named `UpdateLastModified`
CREATE TRIGGER UpdateLastModified  

-- This trigger will execute before an update operation on the Employees table
BEFORE UPDATE ON Employees  

-- Ensures that the trigger runs for each row being updated
FOR EACH ROW  

BEGIN
    -- Automatically update the LastModified column with the current timestamp
    SET NEW.LastModified = NOW();  
END //  

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

Explanation:

• Purpose of the Query:
• The goal is to automatically update the LastModified column whenever an employee record is updated.
• Key Components:
• BEFORE UPDATE: Specifies when the trigger should execute.
• SET NEW.LastModified = NOW(): Updates the LastModified column.
• Why use Triggers?:
• Triggers automate actions based on database events, ensuring data consistency.
• Real-World Application:
• For example, in a HR system, you might use a trigger to track when employee records were last modified.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a trigger that updates the LastModified column in the Customers table whenever a row is updated.
• Write a MySQL query to create a trigger that updates the LastModified column in the Orders table whenever a row is updated.
• Write a MySQL query to create a trigger that updates the LastModified column in the Products table whenever a row is updated.
• Write a MySQL query to create a trigger that updates the LastModified column in the Departments table whenever a row is updated.

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

Previous MySQL Exercise: Create a Trigger to Update Last Modified Date.
Next MySQL Exercise: Create a Stored Procedure to Count Employees in a Department.