Enforce Salary Constraints with a MySQL Trigger

Create a Trigger to Prevent Invalid Salary Updates

Write a MySQL query to create a trigger that prevents updating an employee's salary to a negative value.

Solution:

-- Change the delimiter to allow multi-line trigger definition
DELIMITER //

-- Create a trigger named `PreventNegativeSalary`
CREATE TRIGGER PreventNegativeSalary  -- Trigger name
BEFORE UPDATE ON Employees            -- Trigger fires before an update on the Employees table
FOR EACH ROW                         -- The trigger will execute for each row affected by the update
BEGIN
    -- Check if the new salary is negative
    IF NEW.Salary < 0 THEN  -- Compares the new salary (after update) with 0
        -- Raise an error and prevent the update
        SIGNAL SQLSTATE '45000'  -- Signals a custom error
        SET MESSAGE_TEXT = 'Salary cannot be negative';  -- Custom error message
    END IF;
END //

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

Explanation:

• Purpose of the Query:
• The goal is to enforce business rules using a trigger.
• Key Components:
• BEFORE UPDATE: Specifies when the trigger should execute.
• SIGNAL SQLSTATE: Raises an error to prevent invalid updates.
• 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 prevent invalid salary updates.

For more Practice: Solve these Related Problems:

• Write a MySQL query to create a trigger that prevents updating a department's budget to a negative value.
• Write a MySQL query to create a trigger that prevents updating a project's deadline to a past date.
• Write a MySQL query to create a trigger that prevents updating a customer's age to a negative value.
• Write a MySQL query to create a trigger that prevents updating an order's quantity to zero.

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

Previous MySQL Exercise: Create a Trigger to Log Salary Changes.
Next MySQL Exercise: Create a Stored Procedure to Calculate Average Salary.