MySQL - Before Update Trigger

Using BEFORE UPDATE Triggers for Data Validation

In database systems, maintaining data integrity is paramount. While CHECK constraints are useful for simple rules, BEFORE UPDATE triggers provide a powerful mechanism to enforce complex business logic and validation before a row is modified. This ensures that invalid data never enters your table.

The MySQL BEFORE UPDATE Trigger

A BEFORE UPDATE trigger is a row-level trigger that executes automatically for each row just before an UPDATE operation is performed. Its primary strength lies in its ability to inspect and even modify the incoming data before it’s written to the table.

Key Concept: OLD vs. NEW. Inside an UPDATE trigger, you have access to two special aliases: OLD refers to the row’s values before the update, and NEW refers to the new values being proposed. For example, OLD.salary is the current salary, and NEW.salary is the proposed new salary. You can read from both, but you can only modify NEW values.

Syntax

The syntax for creating a BEFORE UPDATE trigger is straightforward:

CREATE TRIGGER trigger_name
    BEFORE UPDATE ON table_name
    FOR EACH ROW
BEGIN
    -- Trigger logic here. Access data with OLD.column and NEW.column.
    -- To reject an update, use the SIGNAL statement.
END;

Example: Enforcing a Business Rule

Let’s consider a practical scenario. We have an employees table, and we want to enforce a business rule: an employee’s salary can never be decreased. A BEFORE UPDATE trigger is the perfect tool for this.

Step 1: Create the employees table.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL CHECK (salary > 0)
);

Step 2: Insert some initial data.

INSERT INTO employees (name, position, salary) VALUES
('John Doe', 'Software Engineer', 75000.00),
('Jane Smith', 'Project Manager', 90000.00);

Step 3: Create the BEFORE UPDATE trigger to prevent salary decreases.

DELIMITER //

CREATE TRIGGER prevent_salary_decrease
    BEFORE UPDATE ON employees
    FOR EACH ROW
BEGIN
    -- Compare the proposed new salary with the old salary
    IF NEW.salary < OLD.salary THEN
        -- If the new salary is lower, reject the update with a custom error
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'A salary decrease is not permitted.';
    END IF;
END//

DELIMITER ;

Understanding SIGNAL: The SIGNAL statement is how you intentionally raise an error from within a trigger or stored procedure. SQLSTATE '45000' is a generic state code for ‘unhandled user-defined exception’, which is perfect for custom business rule violations.

Testing the Trigger

Now, let’s test our trigger with two UPDATE statements.

Attempt 1: A valid salary increase. This should succeed.

UPDATE employees SET salary = 80000.00 WHERE name = 'John Doe';

Result: Query OK, 1 row affected. The update is successful.

Attempt 2: An invalid salary decrease. This should be blocked by the trigger.

UPDATE employees SET salary = 85000.00 WHERE name = 'Jane Smith';

Result: The database returns an error, and the update is cancelled.

ERROR 1644 (45000): A salary decrease is not permitted.

Handling Trigger Errors in Application Code

A crucial part of using validation triggers is ensuring your application can gracefully handle the errors they produce. Instead of crashing, your application should catch the database error and provide meaningful feedback to the user.

Best Practices for Error Handling

• Use Try-Catch Blocks: Always wrap your database operations in a try-catch (or equivalent) block.
• Inspect the Error: In the catch block, inspect the error object. You can check for the specific SQLSTATE (‘45000’) or the error message text to identify your custom business rule violation.
• Provide User Feedback: Convert the technical database error into a user-friendly message, like “Error: You cannot decrease an employee’s salary.”

Python
Node.js
Java
PHP



This Python example shows how to catch the `DatabaseError` and inspect its properties to provide specific feedback.

```python
import mysql.connector
import os

def update_employee_salary(name, new_salary):
    """Attempts to update an employee's salary and handles trigger errors."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database='your_database'
        )
        cursor = connection.cursor()

        update_query = "UPDATE employees SET salary = %s WHERE name = %s"
        cursor.execute(update_query, (new_salary, name))
        connection.commit()
        print(f"Successfully updated salary for {name}.")

    except mysql.connector.Error as err:
        # Check for our custom trigger error
        if err.sqlstate == '45000':
            print(f"Business Rule Violation: {err.msg}")
        else:
            print(f"A database error occurred: {err}")
        if connection:
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# --- Run the example ---
print("--- Attempting a valid update ---")
update_employee_salary('John Doe', 95000.00)

print("\n--- Attempting an invalid update ---")
update_employee_salary('Jane Smith', 80000.00)

This Node.js example uses async/await within a try...catch block to handle the error from the trigger cleanly.

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

async function updateEmployeeSalary(name, newSalary) {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST || 'localhost',
            user: process.env.DB_USER || 'root',
            password: process.env.DB_PASSWORD || 'password',
            database: 'your_database'
        });

        const updateQuery = 'UPDATE employees SET salary = ? WHERE name = ?';
        await connection.execute(updateQuery, [newSalary, name]);
        console.log(`Successfully updated salary for ${name}.`);

    } catch (error) {
        // Check for our custom trigger error
        if (error.sqlState === '45000') {
            console.error(`Business Rule Violation: ${error.message}`);
        } else {
            console.error(`A database error occurred: ${error.message}`);
        }
    } finally {
        if (connection) await connection.end();
    }
}

// --- Run the example ---
(async () => {
    console.log('--- Attempting a valid update ---');
    await updateEmployeeSalary('John Doe', 96000.00);

    console.log('\n--- Attempting an invalid update ---');
    await updateEmployeeSalary('Jane Smith', 81000.00);
})();

This Java example uses a try-catch block and checks the SQLException’s SQLState to identify the custom error.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateTriggerExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void updateEmployeeSalary(String name, double newSalary) {
        String updateSQL = "UPDATE employees SET salary = ? WHERE name = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {

            pstmt.setDouble(1, newSalary);
            pstmt.setString(2, name);
            pstmt.executeUpdate();
            System.out.println("Successfully updated salary for " + name);

        } catch (SQLException e) {
            // Check for our custom trigger error
            if ("45000".equals(e.getSQLState())) {
                System.err.println("Business Rule Violation: " + e.getMessage());
            } else {
                System.err.println("SQL Error: " + e.getMessage());
            }
        }
    }

    public static void main(String[] args) {
        System.out.println("--- Attempting a valid update ---");
        updateEmployeeSalary("John Doe", 97000.00);

        System.out.println("\n--- Attempting an invalid update ---");
        updateEmployeeSalary("Jane Smith", 82000.00);
    }
}

This modern PHP example uses mysqli in an object-oriented style with exception handling to catch the trigger’s error.

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'your_database';

// Enable mysqli to throw exceptions for errors
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

function updateEmployeeSalary(string $name, float $newSalary) {
    global $dbhost, $dbuser, $dbpass, $dbname;
    $mysqli = null;
    try {
        $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
        $stmt = $mysqli->prepare("UPDATE employees SET salary = ? WHERE name = ?");
        $stmt->bind_param("ds", $newSalary, $name);
        $stmt->execute();
        echo "Successfully updated salary for $name.\n";
    } catch (mysqli_sql_exception $e) {
        // Check for our custom trigger error
        if ($e->getCode() == 1644) { // 1644 is the error number for a signalled state
            echo "Business Rule Violation: " . $e->getMessage() . "\n";
        } else {
            echo "Database Error: " . $e->getMessage() . "\n";
        }
    } finally {
        if ($mysqli) {
            $mysqli->close();
        }
    }
}

// --- Run the example ---
echo "--- Attempting a valid update ---\n";
updateEmployeeSalary('John Doe', 98000.00);

echo "\n--- Attempting an invalid update ---\n";
updateEmployeeSalary('Jane Smith', 83000.00);

?>