Creating a Trigger to Enforce Business Rules

Create a Trigger for Enforcing Business Rules

Write a SQL query to create a trigger that enforces a business rule (e.g., preventing negative salary updates).

Solution:

-- Create a trigger to enforce a business rule.
CREATE TRIGGER trg_PreventNegativeSalary
ON Employees -- Specify the table to monitor.
FOR UPDATE -- Trigger fires after an update operation.
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE Salary < 0)
    BEGIN
        RAISERROR('Negative salary values are not allowed.', 16, 1);
        ROLLBACK TRANSACTION; -- Undo the invalid update.
    END
END;

Explanation:

1. Purpose of the Query :

1. The goal is to create a trigger that prevents invalid updates, such as setting an employee's salary to a negative value.

2. Key Components :

1. CREATE TRIGGER : Defines the trigger.
2. FOR UPDATE : Specifies when the trigger should fire.
3. RAISERROR : Prevents invalid updates by raising an error.

3. Why Use Triggers for Business Rules?

1. Triggers automate the enforcement of business rules, ensuring data integrity without requiring manual checks.

4. Real-World Application :

1. For example, in financial systems, you might use this trigger to prevent invalid or malicious updates to salary data.

Additional Notes:

• Be cautious when using triggers, as they can introduce hidden logic that may be difficult to debug.
• Use this exercise to highlight the importance of validating data at the database level.

For more Practice: Solve these Related Problems:

• Write a SQL query to create a trigger that enforces a rule preventing the insertion of future dates in a specific column.
• Write a SQL query to create a trigger that ensures no two records have overlapping date ranges in a scheduling table.
• Write a SQL query to create a trigger that restricts updates to a table unless they come from a specific user role.
• Write a SQL query to create a trigger that validates data against a set of predefined rules before allowing an insert or update operation.

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

Previous SQL Exercise: Create a Stored Procedure with Error Handling.
Next SQL Exercise: Create a Scalar Function with Conditional Logic.