MySQL AFTER UPDATE Trigger

Introduction

In this chapter, we will learn about the AFTER UPDATE trigger in MySQL. An AFTER UPDATE trigger is a type of trigger that is executed automatically after an UPDATE operation is performed on a table. This type of trigger is useful for tasks such as logging changes, auditing, or synchronizing data across tables. We will cover the syntax for creating AFTER UPDATE triggers, examples of their usage, and important considerations for using them in MySQL.

Syntax

The basic syntax for creating an AFTER UPDATE trigger in MySQL is:

CREATE TRIGGER trigger_name
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
    -- Trigger body: SQL statements
END;

• trigger_name: The name of the trigger.
• table_name: The name of the table on which the trigger is defined.

Example

Here is an example of how to create an AFTER UPDATE trigger that logs changes to an employees table:

1. Create a Database

CREATE DATABASE company;

2. Select the Database

USE company;

3. Create the Employees and Audit Tables

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

CREATE TABLE employee_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    action VARCHAR(10),
    action_time DATETIME
);

4. Create the AFTER UPDATE Trigger

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary, action, action_time)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary, 'update', NOW());
END;

In this example, the after_employee_update trigger logs any updates to the employees table into the employee_audit table after the update operation occurs.

Full Example

Let’s go through a complete example where we create a database, table, and trigger, and then demonstrate the usage of the AFTER UPDATE trigger.

1. Create a Database

CREATE DATABASE company;

2. Select the Database

USE company;

3. Create the Employees and Audit Tables

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

CREATE TABLE employee_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    action VARCHAR(10),
    action_time DATETIME
);

4. Create the AFTER UPDATE Trigger

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, old_salary, new_salary, action, action_time)
    VALUES (NEW.employee_id, OLD.salary, NEW.salary, 'update', NOW());
END;

5. Insert Data into the Employees Table

INSERT INTO employees (first_name, last_name, hire_date, salary) VALUES
('Rahul', 'Sharma', '2023-07-05', 50000.00),
('Priya', 'Singh', '2023-07-05', 60000.00),
('Amit', 'Kumar', '2023-07-05', 55000.00);

6. Update an Employee’s Salary

UPDATE employees SET salary = 65000.00 WHERE employee_id = 2;

7. Query the Employee Audit Table

SELECT * FROM employee_audit;

Output

In this example, the after_employee_update trigger logs updates to the employees table, recording the employee_id, old salary, new salary, action type (update), and the current timestamp.

Using AFTER UPDATE Trigger for Data Synchronization

You can also use an AFTER UPDATE trigger to synchronize data across tables.

Example: Synchronize Data with Another Table

CREATE TABLE employees_backup (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    UPDATE employees_backup
    SET first_name = NEW.first_name,
        last_name = NEW.last_name,
        hire_date = NEW.hire_date,
        salary = NEW.salary
    WHERE employee_id = NEW.employee_id;
END;

In this example, the after_employee_update trigger ensures that any updates made to the employees table are also reflected in the employees_backup table.

Important Considerations

• Performance: Triggers can affect performance, especially if they contain complex logic or if they fire frequently. Use them judiciously.
• Error Handling: Use the SIGNAL statement to raise custom errors within triggers for data validation and business rule enforcement.
• Testing: Thoroughly test triggers to ensure they perform as expected and do not introduce unwanted side effects.
• Order of Execution: When multiple triggers are defined on the same table for the same event, their order of execution is not guaranteed. Be mindful of this when designing your triggers.

Conclusion

The AFTER UPDATE trigger in MySQL is used for automating tasks and enforcing business rules after data is updated in a table. This chapter covered the syntax for creating AFTER UPDATE triggers, provided examples of their usage, and discussed important considerations.