Practice Creating SQL Server Triggers

The best way to learn triggers is to write a few that enforce a real business rule and then test them with both valid and invalid transactions. In this lesson you’ll practice creating a trigger in SQL Server 2025 that enforces an approval policy tied to employee salary.

Triggers are database objects that execute automatically when a data modification statement (INSERT, UPDATE, or DELETE) targets a table (or in some cases a view). Because triggers run inside the same transaction as the statement that fired them, they can reject a change before commit—making them a useful tool for enforcing business rules that must apply to all callers.

That power comes with responsibility. When you practice writing triggers, keep two principles in mind:

• Write statement-safe (set-based) code: triggers fire once per statement, not once per row.
• Prefer declarative constraints when possible: use triggers for rules that can’t be expressed cleanly with keys, CHECK constraints, or referential integrity.

Business rule scenario

Suppose your company enforces this rule:

No employee is allowed to have a salary greater than $200,000 unless an authorizing manager’s employee ID is recorded with the transaction.

Assume a table named employees contains the following columns:

Column name	Data type
EmployeeID	int
Salary	smallmoney
HireDate	smalldatetime
ApprovalID	int

From a relational-theory standpoint, this is a rule about permissible states of the relation: when Salary exceeds a threshold, a related approval identifier must exist. You could model approvals more formally (for example, with a separate approval table and a foreign key), but for the purpose of this trigger exercise we will enforce the policy directly in a trigger.

In this practice lesson you will:

• create a trigger that blocks invalid INSERTs and UPDATEs,
• use the inserted trigger table to validate the change set,
• raise a clear error message when the rule is violated,
• verify behavior using test statements that modify multiple rows.

Remember: a trigger fires per statement. Your code must handle multi-row operations. That’s why the validation uses EXISTS against inserted.

The trigger below enforces the salary approval rule for both inserts and updates. It rejects any statement that attempts to create or modify an employee row where:

• Salary > 200000 and
• ApprovalID is missing.

CREATE OR ALTER TRIGGER dbo.trg_Employees_SalaryApproval
ON dbo.Employees
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Reject any change-set row that violates the approval rule
    IF EXISTS (
        SELECT 1
        FROM inserted i
        WHERE i.Salary > 200000
          AND (i.ApprovalID IS NULL OR i.ApprovalID = 0)
    )
    BEGIN
        THROW 50010,
              'Business rule violation: Salaries above 200000 require an ApprovalID (authorizing manager).',
              1;
    END
END;
GO

Why this trigger is structured this way:

• AFTER INSERT, UPDATE: the rule applies when rows are created or modified. If the rule does not apply to deletes, don’t include DELETE.
• Set-based EXISTS: handles one row or many rows consistently.
• THROW: clearly signals an error, which causes the statement’s transaction to roll back unless the caller intercepts it.

Run tests that include both valid and invalid statements. Also test multi-row statements, since multi-row handling is one of the most common trigger bugs.

-- Valid insert: salary below threshold
INSERT INTO dbo.Employees (EmployeeID, Salary, HireDate, ApprovalID)
VALUES (1, 75000, GETDATE(), NULL);

-- Invalid insert: salary above threshold, missing approval
INSERT INTO dbo.Employees (EmployeeID, Salary, HireDate, ApprovalID)
VALUES (2, 225000, GETDATE(), NULL);

-- Valid insert: salary above threshold, approval provided
INSERT INTO dbo.Employees (EmployeeID, Salary, HireDate, ApprovalID)
VALUES (3, 225000, GETDATE(), 101);

-- Invalid update: increase salary above threshold without approval
UPDATE dbo.Employees
SET Salary = 240000,
    ApprovalID = NULL
WHERE EmployeeID = 1;

-- Multi-row test: update many rows (trigger must handle set logic)
UPDATE dbo.Employees
SET Salary = Salary + 10000
WHERE HireDate >= '2020-01-01';

If an invalid statement is executed, SQL Server will raise the trigger’s error and reject the transaction. Your job during practice is to verify that the database never commits an invalid state.

• Prefer constraints for simple rules: if the rule is a pure predicate on a single row, a CHECK constraint is often faster and clearer.
• Triggers can affect write performance: keep the logic minimal and index any lookup columns you reference.
• Be careful with side effects: avoid sending emails or calling external systems from triggers; prefer async patterns for notifications.
• Document the rule: treat triggers as critical infrastructure—future developers will need the “why,” not only the “what.”

Click the Exercise link below to practice creating triggers.
Creating SQL Server Triggers - Exercise