MSDN Documentation

SQL Triggers: Automating Database Actions

SQL Triggers are special stored procedures that automatically execute or fire in response to certain events on a particular table or view in a database. They are commonly used to maintain the integrity of data, enforce business rules, and automate complex auditing procedures.

What are SQL Triggers?

A trigger is a set of SQL statements that are invoked when an INSERT, UPDATE, or DELETE statement is executed against a table. You can think of them as an automated response mechanism for data modifications. Triggers can be defined to fire:

• BEFORE: Executes its statements before the triggering statement's actions.
• AFTER: Executes its statements after the triggering statement's actions.
• INSTEAD OF: Replaces the triggering statement's actions, executing the trigger's statements instead. This is particularly useful for complex views.

Key Concepts

• Trigger Event: The DML (Data Manipulation Language) operation (INSERT, UPDATE, DELETE) that causes the trigger to fire.
• Trigger Timing: Whether the trigger fires BEFORE, AFTER, or INSTEAD OF the trigger event.
• Trigger Scope: The table or view on which the trigger is defined.
• Trigger Body: The SQL statements that the trigger executes.

Creating a Basic Trigger

Let's consider a scenario where we want to log changes made to an `Employees` table into an `EmployeeAudit` table.

Example Schema

Assume we have the following tables:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary DECIMAL(10, 2)
);

CREATE TABLE EmployeeAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    ActionType VARCHAR(10),
    OldSalary DECIMAL(10, 2),
    NewSalary DECIMAL(10, 2),
    ChangeDate DATETIME DEFAULT GETDATE()
);
            

Trigger for Salary Updates

This trigger will record any salary changes in the Employees table.

CREATE TRIGGER trg_EmployeeSalaryUpdate
ON Employees
AFTER UPDATE OF Salary
AS
BEGIN
    IF UPDATE(Salary)
    BEGIN
        INSERT INTO EmployeeAudit (EmployeeID, ActionType, OldSalary, NewSalary)
        SELECT
            i.EmployeeID,
            'UPDATE',
            d.Salary,
            i.Salary
        FROM
            inserted i
        INNER JOIN
            deleted d ON i.EmployeeID = d.EmployeeID
        WHERE
            i.Salary <> d.Salary; -- Only log if salary actually changed
    END
END;
                

Understanding inserted and deleted Tables

When a trigger fires, SQL Server creates two virtual, temporary tables in memory: inserted and deleted.

• inserted: Contains the new rows that are inserted or the updated values of rows.
• deleted: Contains the old rows that are deleted or the old values of rows before an update.

These tables are crucial for comparing old and new data within a trigger, allowing for detailed logging and complex business rule enforcement.

Common Trigger Use Cases

• Auditing: Tracking data modifications for security or compliance.
• Data Integrity: Enforcing complex validation rules that cannot be handled by constraints alone.
• Referential Integrity: Maintaining consistency between related tables, especially in scenarios not covered by foreign keys.
• Automated Calculations: Updating summary or calculated fields when underlying data changes.

Considerations and Best Practices

• Performance: Triggers add overhead to DML operations. Keep trigger logic as efficient as possible. Avoid complex queries or cursors within triggers if alternatives exist.
• Recursion: Be mindful of recursive triggers (triggers that fire themselves). SQL Server has settings to control or disallow trigger recursion.
• Order of Execution: For multiple triggers on the same table and event, their execution order can be managed using sp_settriggerorder.
• Testing: Thoroughly test triggers with various scenarios, including bulk operations and edge cases.
• Complexity: Avoid overusing triggers. Sometimes, application-level logic or stored procedures can be a cleaner solution.

Triggers are powerful tools for database management, but they should be used judiciously. Understanding their behavior and potential impact on performance is key to effective implementation.