BEFORE UPDATE Trigger - (MySQL Triggers)

A trigger is a piece of code that is executed automatically in response to a specific action, such as an insert, update, or delete operation on a database table. In this tutorial, we'll discuss the BEFORE UPDATE trigger in MySQL.

Syntax

CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name
FOR EACH ROW
BEGIN
   -- trigger logic
END;

In the above syntax:

• trigger_name - is the name of the trigger.
• table_name - is the name of the table on which the trigger is defined.
• FOR EACH ROW - indicates that the trigger is executed for each row that is affected by the update operation.
• BEGIN and END - enclose the trigger logic.

Example

Let's say we have a table called employees, with the following columns:

• id - the id of the employee (primary key).
• name - the name of the employee.
• salary - the salary of the employee.

We want to create a BEFORE UPDATE trigger that automatically updates the updated_at column whenever the salary column is updated. Here's how we can implement it:

CREATE TRIGGER before_update_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary <> OLD.salary THEN
    SET NEW.updated_at = NOW();
  END IF;
END;

In the above example, we create a trigger called before_update_salary that is executed before an update operation is performed on the employees table. The trigger logic checks if the salary column has been updated and if so, updates the updated_at column with the current date and time (using the NOW() function).

Explanation

The BEFORE UPDATE trigger is executed before an update operation is performed, for each row that is affected by the update operation. In the above example, we create a trigger that automatically updates the updated_at column whenever the salary column is updated.

We use the IF statement to check if the new value of the salary column is different from the old value (NEW.salary <> OLD.salary). If it is, we update the updated_at column by setting its value to the current date and time using the NOW() function (SET NEW.updated_at = NOW()).

Use

The BEFORE UPDATE trigger can be used to automatically perform actions whenever a record is updated. This can be useful for implementing audit trails, or for updating related records when a record is updated.

Important Points

• The BEFORE UPDATE trigger is executed before an update operation is performed.
• The trigger logic must be enclosed within a BEGIN and END block.
• The NEW and OLD keywords can be used to access the new and old values of the updated columns.
• Care should be taken when implementing triggers, as they can have a performance impact on the database.

Summary

In this tutorial, we discussed the syntax and example of the BEFORE UPDATE trigger in MySQL. We also explained its explanation, use, and important points. Triggers can be powerful tools for automating tasks in a database, but they should be used with caution and care.