MySQL AFTER UPDATE Trigger

An AFTER UPDATE trigger in MySQL is a type of trigger that is fired after an UPDATE operation is performed on a table. It allows you to execute additional logic or actions after a row is updated. This can be useful for tasks such as logging changes, updating related tables, or auditing.

Syntax for Creating an AFTER UPDATE Trigger

CREATE TRIGGER trigger_name
AFTER UPDATE
ON table_name
FOR EACH ROW
BEGIN
   -- Trigger logic here
END;

trigger_name: Name of the trigger.
AFTER UPDATE: Specifies that the trigger is fired after an update operation.
table_name: The table on which the trigger is defined.
FOR EACH ROW: Indicates that the trigger will be executed for each row affected by the update.
BEGIN ... END;: Contains the logic or action you want to perform after the update.

Using the `OLD` and `NEW` Keywords

In an AFTER UPDATE trigger, you can use the OLD and NEW keywords:

OLD.column_name: Refers to the value of the column before the update.
NEW.column_name: Refers to the value of the column after the update.

Example 1: Logging Changes After Update

In this example, we log every update to the employees table into an audit_log table.

1. Create the `audit_log` Table

CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    update_time DATETIME
);

2. Create the `AFTER UPDATE` Trigger

CREATE TRIGGER log_salary_update
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
   IF OLD.salary != NEW.salary THEN
       INSERT INTO audit_log (employee_id, old_salary, new_salary, update_time)
       VALUES (NEW.employee_id, OLD.salary, NEW.salary, NOW());
   END IF;
END;

Explanation:
- This trigger is activated after an update operation on the employees table.
- It checks if the salary field has changed. If so, it inserts a log entry into the audit_log table, recording the employee_id, the old salary, the new salary, and the current timestamp.

Example 2: Update Related Table After Update

In this example, after updating the price of a product in the products table, we update the total_value in the orders table that references this product.

1. Create the `products` and `orders` Tables

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    price DECIMAL(10, 2)
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    quantity INT,
    total_value DECIMAL(10, 2),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

2. Create the `AFTER UPDATE` Trigger

CREATE TRIGGER update_order_total
AFTER UPDATE
ON products
FOR EACH ROW
BEGIN
   UPDATE orders
   SET total_value = NEW.price * orders.quantity
   WHERE product_id = NEW.product_id;
END;

Explanation:
- This trigger is fired after the price of a product in the products table is updated.
- It updates the total_value of all orders in the orders table that reference the updated product, based on the new price and the quantity in the order.

Example 3: Sending an Email Notification After an Update

Suppose you want to send an email notification after an employee's status is updated in the employees table. While MySQL cannot directly send emails, you can use a trigger to log this event in an external table or system, which could then trigger the email in the application layer.

1. Create an `email_notifications` Table

CREATE TABLE email_notifications (
    notification_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    message VARCHAR(255),
    notification_time DATETIME
);

2. Create the `AFTER UPDATE` Trigger

CREATE TRIGGER notify_status_change
AFTER UPDATE
ON employees
FOR EACH ROW
BEGIN
   IF OLD.status != NEW.status THEN
       INSERT INTO email_notifications (employee_id, message, notification_time)
       VALUES (NEW.employee_id, CONCAT('Employee status changed to ', NEW.status), NOW());
   END IF;
END;

Explanation:
- This trigger checks if the a status column has changed.
- If the status has changed, it inserts a record into the email_notifications table with the employee_id, a message, and the current timestamp. The actual email can be sent by an application that monitors the email_notifications table.

Use Cases for `AFTER UPDATE` Triggers

Auditing and Logging:
- Track changes to important columns in a table, such as prices, employee salaries, or status changes.
Data Integrity:
- Ensure that changes in one table trigger updates in related tables, maintaining consistency across the database.
Business Logic Enforcement:
- Enforce business rules, such as sending notifications, recalculating values, or updating related records after specific updates.
Automated Tasks:
- Perform background tasks automatically after data changes, such as updating stock levels, recalculating totals, or initiating workflows.

Performance Considerations

Impact on Performance: Triggers, especially those performing complex operations or involving multiple rows, can slow down operations like UPDATE. Ensure that triggers are optimized and avoid unnecessary computations.
Avoid Circular Triggers: Be careful not to create a situation where a trigger causes an update that fires the same trigger again, leading to infinite loops.

Conclusion

The AFTER UPDATE trigger in MySQL is a useful tool for automatically performing actions after an update occurs on a table. It is particularly useful for auditing, maintaining data integrity, and triggering automated tasks. When designing triggers, be mindful of performance and the potential for circular dependencies.

Souy Soeng

MySQL AFTER UPDATE Trigger