How to Raise Exceptions in MySQL Triggers

How to raise exceptions in MySQL triggers?

When you need to enforce business rules or validate data in MySQL triggers, you'll want to raise custom exceptions. MySQL provides the SIGNAL statement for this purpose. Here's how to use it effectively.

Basic Syntax

The basic syntax is straightforward:

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Your custom error message';

SQLSTATE codes from '45000' to '49999' are reserved for user-defined errors. When SIGNAL is executed, it immediately stops the current operation and rolls back the transaction.

Example 1: Prevent Negative Values

A common use case is preventing invalid data from being inserted:

DELIMITER $$

CREATE TRIGGER prevent_negative_salary 
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Salary cannot be negative';
    END IF;
END$$

DELIMITER ;

Now if someone tries to insert a negative salary, they'll get your custom error message and the insert will be blocked.

Example 2: Age Validation

Enforce business rules like minimum age requirements:

DELIMITER $$

CREATE TRIGGER validate_employee_age
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45001' 
        SET MESSAGE_TEXT = 'Employee must be at least 18 years old';
    END IF;
END$$

DELIMITER ;

Notice I used '45001' instead of '45000'. Using different SQLSTATE codes helps you identify which validation failed when debugging.

Example 3: Complex Budget Check

You can perform more complex validation by querying other tables:

DELIMITER $$

CREATE TRIGGER check_department_budget
BEFORE INSERT ON employee_assignments
FOR EACH ROW
BEGIN
    DECLARE current_budget DECIMAL(10,2);
    DECLARE total_salaries DECIMAL(10,2);
    DECLARE new_salary DECIMAL(10,2);
    
    -- Get department budget
    SELECT budget INTO current_budget 
    FROM departments 
    WHERE department_id = NEW.department_id;
    
    -- Get current total salaries
    SELECT COALESCE(SUM(salary), 0) INTO total_salaries
    FROM employees e
    JOIN employee_assignments ea ON e.employee_id = ea.employee_id
    WHERE ea.department_id = NEW.department_id;
    
    -- Get new employee's salary
    SELECT salary INTO new_salary
    FROM employees 
    WHERE employee_id = NEW.employee_id;
    
    -- Check if we'd exceed budget
    IF (total_salaries + new_salary) > current_budget THEN
        SIGNAL SQLSTATE '45002'
        SET MESSAGE_TEXT = 'Adding this employee would exceed department budget';
    END IF;
END$$

DELIMITER ;

Key Points to Remember

• Use SQLSTATE '45000' to '49999' - These are reserved for user-defined errors
• Different codes for different errors - Makes debugging easier
• Keep error messages clear - Users need to understand what went wrong
• SIGNAL rolls back everything - The entire transaction is cancelled
• Works in BEFORE and AFTER triggers - Though BEFORE is more common

Common SQLSTATE Patterns

I typically organize my error codes like this:

• 45000-45099 - Validation errors (negative values, wrong formats)
• 45100-45199 - Business logic errors (budget exceeded, age restrictions)
• 45200-45299 - Permission/authorization errors
• 45900-45999 - Catch-all for misc errors

Testing Your Triggers

After creating a trigger, test it to make sure it works:

-- This should fail with your custom error
INSERT INTO employees (name, salary, age) 
VALUES ('John Doe', -5000, 25);

-- Error: Salary cannot be negative

-- This should also fail
INSERT INTO employees (name, salary, age) 
VALUES ('Jane Doe', 50000, 16);

-- Error: Employee must be at least 18 years old

Debugging Tips

If your trigger isn't working as expected:

• Check the trigger exists: SHOW TRIGGERS FROM your_database;
• Verify the DELIMITER is set correctly before creating the trigger
• Make sure your IF condition logic is correct
• Test the SELECT queries independently to verify they return expected values
• Use different SQLSTATE codes so you know which error fired

When to Use SIGNAL vs Constraints

Use database constraints for:

• Simple validations (NOT NULL, UNIQUE, CHECK)
• Foreign key relationships
• Standard data type enforcement

Use SIGNAL in triggers for:

• Complex business logic spanning multiple tables
• Conditional validation based on other data
• Custom error messages that help users understand what's wrong
• Validations that require calculations