AFTER UPDATE triggers in MySQL

Introduction

AFTER UPDATE triggers run automatically after a row in a table is changed. They help you do extra tasks right after data updates.

You want to log changes made to a table for tracking.

You need to update related tables after a record changes.

You want to send notifications after data is updated.

You want to enforce extra rules or calculations after updates.

Syntax

MySQL

CREATE TRIGGER trigger_name
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
  -- statements to run after update
END;

The trigger runs once for each updated row.

Use NEW.column_name to access the new values after update.

Examples

This trigger adds a record to audit_log every time an employee's data is updated.

MySQL

CREATE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(employee_id, changed_at)
  VALUES (NEW.id, NOW());
END;

This trigger saves old and new salary values after a salary update.

MySQL

CREATE TRIGGER update_salary_history
AFTER UPDATE ON salaries
FOR EACH ROW
BEGIN
  INSERT INTO salary_history(employee_id, old_salary, new_salary, changed_on)
  VALUES (OLD.employee_id, OLD.salary, NEW.salary, NOW());
END;

Sample Program

This example creates two tables: products and price_changes. The trigger after_product_update runs after a product's price changes and records the old and new prices with the time. Then we insert a product and update its price to see the trigger in action.

MySQL

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2)
);

CREATE TABLE price_changes (
  product_id INT,
  old_price DECIMAL(10,2),
  new_price DECIMAL(10,2),
  changed_at DATETIME
);

DELIMITER $$
CREATE TRIGGER after_product_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO price_changes(product_id, old_price, new_price, changed_at)
  VALUES (OLD.id, OLD.price, NEW.price, NOW());
END$$
DELIMITER ;

INSERT INTO products VALUES (1, 'Pen', 1.00);
UPDATE products SET price = 1.20 WHERE id = 1;
SELECT * FROM price_changes;

Important Notes

Remember to set DELIMITER when creating triggers in MySQL to avoid syntax errors.

OLD.column_name refers to the value before update; NEW.column_name is the value after update.

AFTER UPDATE triggers cannot change the row that fired them.

Summary

AFTER UPDATE triggers run automatically after a row is updated.

They help perform extra actions like logging or updating related data.

Use OLD and NEW to access old and new row values inside the trigger.