MySQL AFTER UPDATE Trigger

Overview

An AFTER UPDATE trigger in MySQL is a specialized stored routine that automatically executes a specified action after an UPDATE operation is performed on a table. This functionality is essential for maintaining data integrity, logging changes, and enforcing business rules.

Key Concepts

• Trigger: A database object that is automatically executed in response to specific events on a designated table.
• AFTER UPDATE: Indicates that the trigger activates following an UPDATE operation on the table.
• Syntax: The basic syntax for creating an AFTER UPDATE trigger is as follows:

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

How It Works

1. Definition: Define a trigger to perform specific actions when data in a table is updated.
2. Execution: The trigger is automatically invoked after an UPDATE statement is executed.
3. Scope: The actions defined in the trigger can access both the old values (before the update) and the new values (after the update) using the OLD and NEW keywords.

Example

Creating a Sample Table

Before creating a trigger, you need a table. Here’s a simple example of a users table:

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

Creating an AFTER UPDATE Trigger

To log changes to user emails, you can create an AFTER UPDATE trigger. Here’s how:

CREATE TABLE email_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    old_email VARCHAR(100),
    new_email VARCHAR(100),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

Explanation of the Example

• Table email_log: This table stores logs of email changes, including the old and new email addresses along with the timestamp of the change.
• Trigger Logic: The trigger checks if the email has changed (OLD.email <> NEW.email). If it has, it inserts a record into the email_log table.

Conclusion

AFTER UPDATE triggers in MySQL are powerful tools for automating actions in response to data changes. They help enforce rules, maintain logs, and ensure data integrity without requiring additional application logic. By understanding how to create and utilize these triggers, you can significantly enhance the functionality of your databases.