Practice Modifying Triggers in SQL Server 2025

In a relational database system, business rules often evolve as organizational policies change. Triggers allow the database to automatically enforce these rules whenever data is modified. In this lesson, you will examine how an existing SQL Server trigger can be modified to enforce a new business rule governing employee salary approval. Understanding how to safely update trigger logic is an important skill for database administrators and developers responsible for maintaining data integrity.

Earlier in this module, a trigger named trgSalary was created on the Employees table. The trigger enforced a business rule stating that an employee could not receive a salary greater than $200,000 unless the transaction included the employee identifier of a manager who approved the salary. The company has now changed its policy: managerial authorization is required for any salary greater than $150,000.

Because the business rule changed, the trigger must be modified so that it validates the new threshold. The Employees table used in this example contains the following columns.

Modifying the salary validation trigger

Modern versions of SQL Server allow developers to update trigger logic using the CREATE OR ALTER TRIGGER statement. This approach replaces the existing trigger definition without requiring the trigger to be dropped and recreated.

The following trigger demonstrates how the updated business rule can be enforced. Whenever a row is inserted into the Employees table, the trigger evaluates the inserted logical table to determine whether the salary exceeds the allowed threshold and whether managerial approval has been provided.

CREATE OR ALTER TRIGGER trgSalary
ON employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT 1
        FROM inserted
        WHERE Salary > 150000
        AND ApprovalID IS NULL
    )
    BEGIN
        THROW 51000,
        'ApprovalID is required for salaries exceeding $150,000.',
        1;
    END
END;
GO

This trigger ensures that any employee inserted with a salary greater than $150,000 must include a valid approval identifier. If the rule is violated, SQL Server raises an error and the transaction fails. Because triggers execute within the same transaction as the data modification statement, invalid data never becomes part of the persistent database state.

Using INSTEAD OF triggers for validation

Another approach is to use an INSTEAD OF INSERT trigger. Unlike an AFTER INSERT trigger, which executes after the database operation has been attempted, an INSTEAD OF trigger intercepts the modification before it occurs. This design allows the trigger to validate data and selectively insert only valid rows.

CREATE OR ALTER TRIGGER trgSalary
ON employees
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT 1
        FROM inserted
        WHERE Salary > 150000
        AND ApprovalID IS NULL
    )
    BEGIN
        THROW 51000,
        'ApprovalID is required for salaries exceeding $150,000.',
        1;
        RETURN;
    END

    INSERT INTO employees (EmployeeID, Salary, ApprovalID)
    SELECT EmployeeID, Salary, ApprovalID
    FROM inserted;
END;
GO

This version prevents invalid rows from being inserted entirely. If the rule is satisfied, the trigger forwards the valid rows from the inserted pseudo-table into the Employees table. If the rule is violated, the trigger raises an error and the insertion is cancelled.

Triggers and relational data integrity

Although triggers provide powerful mechanisms for enforcing complex business rules, they should be used carefully. Whenever possible, relational database systems prefer declarative constraints such as primary keys, foreign keys, and check constraints. Triggers are typically reserved for rules that require conditional logic, cross-table validation, or auditing functionality.

When modifying triggers in SQL Server 2025, developers should also consider performance implications. Triggers fire once per statement rather than once per row, which means that trigger logic must be written using set-based SQL operations that correctly handle multiple rows in the inserted table.

By examining trigger definitions and modifying them to reflect changing business rules, database professionals gain a deeper understanding of how relational databases enforce data integrity at the system level.

Modifying Triggers – Exercise