w3resource

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.

Go to:


PREV : Create a Trigger to Update Last Modified Date.
NEXT : Create a Stored Procedure to Count Employees in a Department.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.