MySQL UPDATE Trigger Guide

MySQL triggers are a special type of stored procedure that automatically performs operations on a table. When a specified event occurs, the trigger is activated and executes the defined operation.

Here is a detailed explanation of an example of an UPDATE trigger in MySQL.

Assume there is a table named employees, which includes the following columns:

1. Employee ID (primary key)
2. Employee name
3. Employee salary
4. old_salary: used to store the salary before the last update.

We would like to automatically store the old salary when updating employee salaries using a trigger.

CREATE TRIGGER update_salary_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    SET NEW.old_salary = OLD.salary;
END;

The trigger named update_salary_trigger is activated before every update operation on the employees table. The BEFORE UPDATE keyword specifies that the trigger is activated before the update operation.

The FOR EACH ROW keyword specifies that the trigger will be executed once for each row of data.

Between BEGIN and END are the operations of the trigger. In this example, we are using the SET statement to assign the old salary (i.e. OLD.salary) to NEW.old_salary, storing the salary before the update in the old_salary column.

When this trigger is used, when executing the following UPDATE statement:

UPDATE employees
SET salary = 5000
WHERE id = 1;

The trigger will be fired before the update operation, storing the original salary (e.g. 4000) in the old_salary column.

In the end, the updated data for the employees table is as follows:

| id | name  | salary | old_salary |
|----|-------|--------|------------|
| 1  | John  | 5000   | 4000       |
| 2  | Jane  | 3000   | 3000       |
| 3  | Alice | 2000   | 2000       |

This is a detailed explanation of a MySQL trigger UPDATE instance. Triggers can be used to perform various automated operations to meet specific business needs.