w3resource

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.

Go to:


PREV : Call the Stored Procedure to Calculate Total Salary.
NEXT : Create a Stored Procedure to Archive Old Employees.

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

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.