How to Create an AFTER UPDATE Trigger in MySQL

In MySQL, you create an AFTER UPDATE trigger using the CREATE TRIGGER statement followed by AFTER UPDATE ON table_name. This trigger runs automatically after a row in the specified table is updated.

Syntax

The basic syntax to create an AFTER UPDATE trigger in MySQL is:

• CREATE TRIGGER trigger_name: Names the trigger.
• AFTER UPDATE ON table_name: Specifies the trigger fires after an update on the table.
• FOR EACH ROW: Runs the trigger for each updated row.
• BEGIN ... END: Contains the SQL statements to execute.

sql

CREATE TRIGGER trigger_name
AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements here
END

Example

This example creates an AFTER UPDATE trigger that logs changes to a users table into a users_audit table.

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

CREATE TABLE users_audit (
  audit_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  old_email VARCHAR(50),
  new_email VARCHAR(50),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.email <> NEW.email THEN
    INSERT INTO users_audit(user_id, old_email, new_email)
    VALUES (OLD.id, OLD.email, NEW.email);
  END IF;
END$$
DELIMITER ;

-- Update example
UPDATE users SET email = '[e-mail]' WHERE id = 1;

Output

Query OK, 0 rows affected (for trigger creation)
Query OK, 1 row affected (for update)

-- After update, users_audit will have a new row logging the email change.

Common Pitfalls

Common mistakes when creating AFTER UPDATE triggers include:

• Not using DELIMITER to change the statement delimiter, causing syntax errors.
• Forgetting FOR EACH ROW, which is required for row-level triggers.
• Not checking if values actually changed, which can cause unnecessary actions.
• Trying to modify the same table that fired the trigger, which can cause recursion errors.

sql

/* Wrong: Missing DELIMITER change causes error */
CREATE TRIGGER after_update_wrong
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_audit(user_id) VALUES (OLD.id);
END;

/* Right: Use DELIMITER to define trigger properly */
DELIMITER $$
CREATE TRIGGER after_update_right
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO users_audit(user_id) VALUES (OLD.id);
END$$
DELIMITER ;

Quick Reference

Part	Description
CREATE TRIGGER trigger_name	Defines the trigger's name
AFTER UPDATE ON table_name	Sets trigger to run after update on the table
FOR EACH ROW	Executes trigger for each updated row
BEGIN ... END	Contains the SQL statements to run
DELIMITER $$ ... DELIMITER ;	Changes delimiter to allow trigger body definition

Key Takeaways

Use CREATE TRIGGER with AFTER UPDATE ON to run code after row updates.

Always include FOR EACH ROW to apply trigger to every updated row.

Use DELIMITER to define multi-statement triggers without syntax errors.

Check OLD and NEW values to act only on actual changes.

Avoid modifying the same table inside its own trigger to prevent recursion.