MySQL - After Update Trigger

MySQL: The AFTER UPDATE Trigger

An AFTER UPDATE trigger is a specific type of database trigger that automatically executes after an UPDATE operation has been successfully completed on a table. This is incredibly useful for tasks like logging changes, updating aggregated data in other tables, or sending notifications after a record has been modified.

Remember, MySQL triggers are row-level, meaning the AFTER UPDATE trigger will fire once for each row that is successfully updated by an UPDATE statement.

Anatomy of an AFTER UPDATE Trigger

When working within an AFTER UPDATE trigger, you have access to both the old and new state of the data for the affected row. This is done through special keywords:

• OLD.column_name: This gives you the value of a column before the update occurred.
• NEW.column_name: This gives you the value of a column after the update occurred.

This dual access is what makes AFTER UPDATE triggers so powerful for auditing and comparison tasks.

Syntax

CREATE TRIGGER trigger_name
    AFTER UPDATE ON table_name
    FOR EACH ROW
BEGIN
    -- Trigger logic here. You can access OLD and NEW values.
END;

Example: Enforcing a Business Rule

While BEFORE UPDATE triggers are often used for validation, AFTER UPDATE triggers can be used to enforce rules or react to changes. Let’s create an example that prevents a user’s age from being updated to a negative value. We’ll use the SIGNAL statement to return a custom, clear error message to the client.

First, let’s create a users table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    age INT NOT NULL
);

Insert a sample user:

INSERT INTO users (username, age) VALUES ('alex_jones', 35);

Now, create the AFTER UPDATE trigger. Note that for this specific validation, a BEFORE UPDATE trigger is more efficient because it can prevent the invalid update from happening at all. However, we use AFTER UPDATE here to demonstrate its syntax and the SIGNAL statement.

DELIMITER //

CREATE TRIGGER after_user_age_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.age < 0 THEN
        -- SQLSTATE '45000' is a generic user-defined error code.
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Validation Error: Age cannot be negative.';
    END IF;
END//

DELIMITER ;

Let’s test the trigger by attempting an invalid update:

UPDATE users SET age = -5 WHERE username = 'alex_jones';

Expected Output

The database will reject the update and return the custom error we defined:

ERROR 1644 (45000): Validation Error: Age cannot be negative.

A valid update, however, will succeed without any errors:

UPDATE users SET age = 36 WHERE username = 'alex_jones';
-- Query OK, 1 row affected

Handling Trigger Errors in a Client Program

A well-written application should be able to catch errors raised by triggers and handle them gracefully. Instead of crashing, it should display a meaningful message to the user.

Syntax Overview

Node.js
Python
Java
PHP



Using `mysql2/promise` with a try...catch block to handle the error:

try {
    const [result] = await connection.execute(updateQuery);
} catch (error) {
    // The error from the trigger will be caught here.
    console.error(`Database Error: ${error.message}`);
}



Using `mysql-connector-python` and its specific error classes:

from mysql.connector import errors

try:
    cursor.execute(update_query)
    connection.commit()
except errors.DatabaseError as e:
    # The trigger's SIGNAL will raise a DatabaseError.
    print(f"Database Error: {e}")



Using JDBC with a try-catch block for `SQLException`:

try (Statement stmt = conn.createStatement()) {
    stmt.executeUpdate(updateSql);
} catch (SQLException e) {
    // Check for the specific SQLSTATE if needed.
    if ("45000".equals(e.getSQLState())) {
        System.err.println("Caught trigger validation error: " + e.getMessage());
    } else {
        e.printStackTrace();
    }
}



Using PDO and catching `PDOException`:

try {
    $pdo->exec($updateSql);
} catch (PDOException $e) {
    // The error message from the trigger is available.
    echo "Database Error: " . $e->getMessage();
}

Complete Example (Node.js)

This Node.js example uses the modern mysql2/promise library to execute an update and properly catch the custom error from our trigger.

const mysql = require('mysql2/promise');

// Best practice: Use environment variables for credentials
const dbConfig = {
    host: 'localhost',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database'
};

async function attemptInvalidUpdate() {
    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        console.log('Connected to the database.');

        const invalidUpdateQuery = "UPDATE users SET age = -5 WHERE username = 'alex_jones';";
        console.log(`Attempting to execute: ${invalidUpdateQuery}`);

        await connection.execute(invalidUpdateQuery);

        console.log('Update was successful (this should not happen).');

    } catch (error) {
        // Catch the error thrown by the trigger's SIGNAL
        if (error.sqlState === '45000') {
            console.error('Caught expected validation error from trigger:');
            console.error(`=> ${error.message}`);
        } else {
            console.error('An unexpected database error occurred:', error);
        }
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

attemptInvalidUpdate();

/*
Expected Output:

Connected to the database.
Attempting to execute: UPDATE users SET age = -5 WHERE username = 'alex_jones';
Caught expected validation error from trigger:
=> Validation Error: Age cannot be negative.
Connection closed.
*/