Enforce Maximum Salary with a MySQL Trigger

Create a Trigger to Enforce Maximum Salary

Write a MySQL query to create a trigger that prevents inserting or updating an employee's salary if it exceeds a maximum value.

Solution:

-- Create a trigger named `EnforceMaxSalary`
DELIMITER //

-- Define the trigger `EnforceMaxSalary`
-- This trigger executes BEFORE an INSERT operation on the `Employees` table
CREATE TRIGGER EnforceMaxSalary
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    -- Check if the new employee's salary exceeds the allowed maximum (100000)
    IF NEW.Salary > 100000 THEN
        -- Raise an MySQL error and prevent the insertion
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot exceed 100000';
    END IF;
END //

-- Reset the delimiter to the default `;`
DELIMITER ; 
   

Explanation:

• Purpose of the Query:
• The goal is to enforce a business rule using a trigger.
• Key Components:
• BEFORE INSERT: Specifies when the trigger should execute.
• SIGNAL SQLSTATE: Raises an error to prevent invalid operations.
• Why use Triggers?:
• Triggers enforce data validation rules at the database level.
• Real-World Application:
• For example, in a payroll system, you might use a trigger to enforce a maximum salary limit.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a trigger that prevents inserting or updating a department's budget if it exceeds a maximum value.
• Write a MySQL query to create a trigger that prevents inserting or updating a product's price if it exceeds a maximum value.
• Write a MySQL query to create a trigger that prevents inserting or updating an order's total amount if it exceeds a maximum value.
• Write a MySQL query to create a trigger that prevents inserting or updating a project's cost if it exceeds a maximum value.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Call the Stored Procedure to Calculate Total Salary.
Next MySQL Exercise: Create a Stored Procedure to Archive Old Employees.