Lesson 4	Enforcing business rules with triggers
Objective	What is considered when enforcing business rules with triggers

Enforcing Business Rules with Triggers

When enforcing "business rules" with triggers in SQL Server 2022, several important considerations must be addressed to ensure performance, maintainability, and data integrity:
✅ Key Considerations:

Complexity and Readability
- Triggers can encapsulate complex logic, but overuse or poor design can make them difficult to debug or maintain.
- Use clear naming conventions and document the purpose of each trigger.
Execution Order and Nested Triggers
- SQL Server allows multiple triggers on a table (AFTER triggers).
- You cannot define execution order among AFTER triggers natively.
- Nested triggers are allowed by default but can be disabled with:
```
          EXEC sp_configure 'nested triggers', 0;
          RECONFIGURE;
        
```
Performance Impact
- Triggers automatically execute during data modification operations (INSERT, UPDATE, DELETE).
- Heavy or poorly optimized logic inside triggers can degrade performance.
- Use the SET NOCOUNT ON command inside triggers to reduce unnecessary network traffic:
```
          CREATE TRIGGER trg_example
          ON MyTable
          AFTER INSERT
          AS
          BEGIN
            SET NOCOUNT ON;
            -- Business rule logic here
          END;
        
```

Unintended Side Effects

Triggers may fire unexpectedly during bulk operations or ETL processes, potentially causing data issues or performance bottlenecks.
Always test triggers thoroughly in integration scenarios.

Use of INSERTED and DELETED Tables

SQL Server uses virtual tables (INSERTED, DELETED) to access before and after images of rows.
Use these for condition checks, validation, and enforcing rules.

Idempotency and Reentrancy

Triggers should be written in a way that avoids repeating actions or creating recursive data changes unintentionally.

Avoiding Business Logic Overlap

Ensure business rules in triggers are not duplicated in:
- Application code
- Stored procedures
- Constraints
Centralizing rules in one layer improves consistency and debugging.

Concurrency and Transactions

Triggers run within the same transaction as the DML operation that fired them.
This can cause locking or blocking issues if not carefully managed.

Use DML vs. DDL Triggers Appropriately

Use DML triggers for enforcing business rules on data.
Use DDL triggers for monitoring or restricting schema changes.

Auditing and Logging

Consider logging trigger activity (e.g., into an audit table), but avoid excessive logging which could impact performance.

Best Practice Summary:

Area	Recommendation
Simplicity	Keep logic minimal inside triggers.
Maintenance	Document all trigger logic clearly.
Performance	Avoid complex joins or cursors; use set-based operations.
Debugging	Test with all relevant DML operations (single-row and multi-row).
Consistency	Don't mix similar business rules in triggers and app code.

Because triggers automatically perform an action after a change is made to your database, they are an important method for enforcing the business rules that govern your database. Business rules govern the conditions that must be met for a company’s policies to be enforced.

SQL Server 2022 Trigger Guidelines

Fully Understand the Rules:
- Absolutely critical. Business rules can be intricate, with edge cases that may not be immediately obvious. For example, a trigger enforcing a rule like "no employee can have a salary increase > 20% in one update" requires understanding all possible scenarios (e.g., null values, negative adjustments). Misunderstanding rules can lead to triggers that fail silently or produce incorrect outcomes.
- In SQL Server 2022, you can leverage tools like SQL Server Management Studio (SSMS) or Azure Data Studio to debug and trace trigger execution, ensuring all conditions are covered.
Determine if a Trigger is the Correct Way to Enforce Rules:
- This is a key consideration. Triggers are powerful but not always the best tool. For instance, simple data integrity rules (e.g., ensuring a column is not null) are better handled by NOT NULL constraints or CHECK constraints, which are more performant and easier to maintain. Foreign key constraints can enforce referential integrity more efficiently than triggers in many cases.
- Stored procedures or application-level logic might be preferable for complex rules that require user interaction or logging. Triggers should typically be reserved for automatic, server-side enforcement of rules that can’t be easily handled elsewhere.
- SQL Server 2022 supports features like In-Memory OLTP and temporal tables, which might offer alternative ways to enforce certain rules (e.g., tracking changes over time) without triggers.
Use Solid Coding Techniques for Maintainability and Readability:
- Triggers can become complex, especially when handling multi-row operations or nested triggers. Writing clear, well-commented code with consistent formatting is essential. For example, always check for the number of rows affected (using @@ROWCOUNT) and handle multi-row inserts/updates correctly to avoid errors.
- In SQL Server 2022, you can use features like the inserted and deleted virtual tables effectively within triggers, but ensure logic is explicit (e.g., avoid assumptions about single-row operations). Modularize complex logic into user-defined functions if needed, and use meaningful trigger names (e.g., trg_EmployeeSalary_Update).
- Avoid excessive nesting of triggers (controlled via sp_configure 'nested_triggers') to keep code manageable.
Fully Test Triggers, Especially for Complex Rules:
- Thorough testing is non-negotiable. Triggers can have unintended side effects, such as performance degradation or blocking operations if poorly designed. Test all scenarios, including edge cases (e.g., bulk inserts, concurrent transactions, or rollbacks).
- SQL Server 2022 provides tools like Query Store and Extended Events to monitor trigger performance and behavior. You can also use SET NOCOUNT ON in triggers to reduce noise during testing and ensure accurate result sets.
- Simulate real-world workloads to verify that triggers don’t introduce deadlocks or excessive locking, especially in high-transaction environments.

Additional Notes:

Performance Considerations: Triggers can impact performance, especially in high-throughput systems. In SQL Server 2022, consider optimizing trigger logic with indexed views or computed columns if the rules involve frequent lookups or calculations.
Security: Ensure triggers respect SQL Server 2022’s security features, like row-level security (RLS) or encryption, if sensitive data is involved.
Documentation: Document the purpose, logic, and dependencies of each trigger to aid future maintenance, as triggers can be overlooked during database refactoring.

These guidelines are sound and align with modern database development practices.

Sample Trigger implementation in SQL Server 2022

Below is a sample Trigger implementation in SQL Server 2022 that demonstrates enforcing a business rule. The scenario is a common one: ensuring that an employee's salary increase does not exceed 20% in a single update, and logging any attempted violations for auditing purposes. This example incorporates the guidelines you provided (understanding rules, appropriate use of triggers, solid coding, and testability).
Scenario

Business Rule: When updating an employee's salary in the Employees table, the new salary cannot exceed 120% of the original salary. If an attempt is made to violate this rule, the update is rolled back, and the attempt is logged in an audit table.
Trigger Type: AFTER UPDATE (DML trigger).
Features Demonstrated: Multi-row handling, error handling, logging, and clear coding practices.

Sample Code

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2) NOT NULL CHECK (Salary > 0),
    LastUpdated DATETIME DEFAULT GETDATE()
);

-- Create an Audit table to log salary update violations
CREATE TABLE SalaryAuditLog (
    AuditID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeID INT,
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2),
    AttemptedBy NVARCHAR(128),
    AttemptDate DATETIME DEFAULT GETDATE(),
    ErrorMessage NVARCHAR(500)
);

-- Create the Trigger
CREATE TRIGGER trg_EmployeeSalary_Update
ON Employees
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON; -- Prevent extra result sets from interfering

    -- Declare variables for error handling
    DECLARE @ErrorMessage NVARCHAR(500);

    -- Check if Salary column was updated
    IF UPDATE(Salary)
    BEGIN
        -- Use a TRY-CATCH block for robust error handling
        BEGIN TRY
            -- Check for salary increase > 20% in the updated rows
            IF EXISTS (
                SELECT 1
                FROM inserted i
                JOIN deleted d ON i.EmployeeID = d.EmployeeID
                WHERE i.Salary > d.Salary * 1.20 -- New salary exceeds 120% of old salary
            )
            BEGIN
                -- Log the violation to the audit table
                INSERT INTO SalaryAuditLog (EmployeeID, OldSalary, NewSalary, AttemptedBy, ErrorMessage)
                SELECT 
                    i.EmployeeID,
                    d.Salary,
                    i.Salary,
                    SUSER_SNAME(), -- Capture the user attempting the update
                    'Salary increase exceeds 20% limit. Old Salary: ' + CAST(d.Salary AS NVARCHAR(20)) + 
                    ', New Salary: ' + CAST(i.Salary AS NVARCHAR(20))
                FROM inserted i
                JOIN deleted d ON i.EmployeeID = d.EmployeeID
                WHERE i.Salary > d.Salary * 1.20;

                -- Raise an error and roll back the transaction
                SET @ErrorMessage = 'Update failed: Salary increase cannot exceed 20% of the original salary.';
                THROW 50001, @ErrorMessage, 1;
            END
            ELSE
            BEGIN
                -- Update LastUpdated column for valid updates
                UPDATE e
                SET LastUpdated = GETDATE()
                FROM Employees e
                INNER JOIN inserted i ON e.EmployeeID = i.EmployeeID;
            END
        END TRY
        BEGIN CATCH
            -- Roll back the transaction if an error occurs
            IF @@TRANCOUNT > 0
                ROLLBACK;

            -- Re-throw the error to the client
            THROW;
        END CATCH
    END
END;
GO

Explanation of the Trigger

Business Rule Enforcement:
- The trigger checks if the Salary column is updated (using IF UPDATE(Salary)).
- It compares the new salary (inserted.Salary) with the old salary (deleted.Salary) to ensure the increase is not more than 20% (i.e., new <= old * 1.20).
- If the rule is violated, the trigger logs the attempt and raises an error to roll back the update.
Solid Coding Practices:
- Multi-row Handling: The trigger uses set-based operations (JOIN between inserted and deleted) to handle multiple updated rows correctly.
- Error Handling: A TRY-CATCH block ensures robust error management, with explicit rollback if needed.
- Clarity: The code is commented, uses meaningful variable names, and follows a logical structure.
- Performance: SET NOCOUNT ON reduces unnecessary result sets, and the logic is optimized to avoid unnecessary checks.
Logging for Auditability:
- Violations are logged in the SalaryAuditLog table, capturing the employee ID, old and new salaries, the user who attempted the change (SUSER_SNAME()), and a descriptive error message.
- This supports traceability and debugging, aligning with the guideline to fully test and understand complex rules.
Appropriate Use of Trigger:
- This rule is well-suited for a trigger because it requires server-side enforcement across all updates (e.g., from applications, scripts, or direct queries) and involves auditing. A constraint alone wouldn’t support logging, and application logic might be bypassed.

Testing the Trigger

You can test the trigger with the following SQL statements:

-- Insert sample data
INSERT INTO Employees (FirstName, LastName, Salary)
VALUES ('John', 'Doe', 50000.00),
       ('Jane', 'Smith', 60000.00);

-- Valid update: Increase salary by 10% (within limit)
UPDATE Employees
SET Salary = 55000.00 -- 50000 * 1.10
WHERE EmployeeID = 1;

-- Check the result
SELECT * FROM Employees; -- Salary updated, LastUpdated set
SELECT * FROM SalaryAuditLog; -- No audit entry

-- Invalid update: Increase salary by 30% (exceeds limit)
UPDATE Employees
SET Salary = 65000.00 -- 50000 * 1.30
WHERE EmployeeID = 1;
-- This will fail with an error and roll back

-- Check the audit log
SELECT * FROM SalaryAuditLog; -- Shows the violation attempt
SELECT * FROM Employees; -- Salary unchanged

Output for Invalid Update
The invalid update will produce an error like:

Msg 50001, Level 16, State 1, Line 1
Update failed: Salary increase cannot exceed 20% of the original salary.

The SalaryAuditLog table will contain a row with details of the failed attempt.
Why This Works for SQL Server 2022

The syntax and features (e.g., inserted/deleted tables, THROW, SUSER_SNAME()) are fully supported in SQL Server 2022.
The trigger leverages modern error-handling practices (TRY-CATCH) and is optimized for performance, aligning with SQL Server 2022’s capabilities.
It is testable and maintainable, with clear logging for debugging or compliance.

Transaction Flow

Triggers affect the transactional state in which they are fired. Knowing these effects can prevent conflicts with constraints, locking, and blocking on the affected tables. Every transaction invokes various checks in the following order:

IDENTITY INSERT check.
Nullability constraint.
Data-type check.
INSTEAD OF trigger execution. If an INSTEAD OF trigger exists, then execution of the DML stops here. INSTEAD OF triggers are not recursive. (Recursive triggers are covered later .)
Primary-key constraint.
Check constraints.
Foreign-key constraints.
DML execution and update to the transaction log.
AFTER trigger execution.
Commit transaction. Based on SQL Server’s transaction flow, keep the following points in mind:

An AFTER trigger occurs after all constraints are enforced. If a constraint is violated, then AFTER triggers are not fired.
An "INSTEAD OF trigger" can circumvent foreign-key violations but not nullability, data-type, or identity-column violations.
The "AFTER trigger" occurs before the DML transaction is committed, so it can roll back the transaction if the data is unacceptable.

Trigger examples
Following are examples of business rules that are good candidates for enforcement with triggers:
1. No employee can have a salary greater than $200,000 without the approval of a manager. The manager’s employee ID must be stored toprove that this salary was authorized.
2. Whenever a sales transaction is greater than $50,000, the company president must be notified so that the salesperson can be personally congratulated.
3. A sale cannot be made for an item that is NOT in inventory, unless the InventoryType field has a value of B.
All these rules can be enforced by using triggers. The rest of this module shows you how to do this.
In the next lesson, the creation of triggers will be discussed.