SQL Triggers

This document provides a comprehensive guide to SQL triggers, their functionality, syntax, and best practices within Microsoft SQL Server.

What is a Trigger?

A trigger is a special type of stored procedure that automatically executes or is fired when an event occurs in the database. These events are typically data manipulation language (DML) statements such as INSERT, UPDATE, or DELETE operations on a specific table.

Types of Triggers

• DML Triggers: Execute in response to DML events (INSERT, UPDATE, DELETE).
• DDL Triggers: Execute in response to DDL events (CREATE, ALTER, DROP, etc.).
• Login Triggers: Execute in response to logon events.

DML Triggers in Detail

DML triggers are the most common type. They can be classified further:

• AFTER Triggers: Execute after the triggering DML statement has completed.
• INSTEAD OF Triggers: Execute instead of the triggering DML statement. These are useful for complex view updates.

Syntax for Creating a DML Trigger

CREATE TRIGGER trigger_name
ON table_name
[ FOR | AFTER | INSTEAD OF ]
[ INSERT, UPDATE, DELETE ]
[ WITH EXECUTE AS { 'user' | SELF | OWNER } ]
AS
BEGIN
    -- Trigger logic goes here
END;

Special Trigger Tables

Within a trigger, you can access two special, undeclared tables:

• inserted: Contains the new or updated rows affected by the DML statement.
• deleted: Contains the old rows affected by the DML statement (for UPDATE and DELETE).

Example: Logging Changes to a Table

CREATE TRIGGER trg_Employee_Update
ON Employees
AFTER UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) -- INSERT operation
    BEGIN
        INSERT INTO AuditLog (TableName, Action, UserID, Timestamp)
        SELECT 'Employees', 'INSERT', inserted.EmployeeID, GETDATE()
        FROM inserted;
    END
    ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- UPDATE operation
    BEGIN
        INSERT INTO AuditLog (TableName, Action, UserID, Timestamp)
        SELECT 'Employees', 'UPDATE', inserted.EmployeeID, GETDATE()
        FROM inserted;
    END
    ELSE IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- DELETE operation
    BEGIN
        INSERT INTO AuditLog (TableName, Action, UserID, Timestamp)
        SELECT 'Employees', 'DELETE', deleted.EmployeeID, GETDATE()
        FROM deleted;
    END
END;

Common Use Cases for Triggers

• Enforcing complex business rules and data integrity.
• Auditing data changes.
• Preventing invalid transactions.
• Automating tasks based on data modifications.
• Synchronizing data across tables.

Best Practices

• Keep trigger logic concise and efficient. Complex logic might be better suited for stored procedures.
• Avoid nesting triggers excessively, as it can lead to performance issues and complex debugging.
• Use IF EXISTS (SELECT * FROM inserted) and IF EXISTS (SELECT * FROM deleted) to check if the trigger fired due to INSERT, UPDATE, or DELETE.
• Handle multiple-row operations correctly by querying the inserted and deleted tables.
• Consider the performance impact of triggers on your database operations.
• Document your triggers thoroughly.

API Reference (Common Commands)

For more advanced trigger types and detailed information, please refer to the official Microsoft SQL Server documentation.