T-SQL Triggers

A trigger is a special type of stored procedure that automatically executes or fires in response to an event in the database. These events can be Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE) or Data Definition Language (DDL) statements (CREATE, ALTER, DROP). Triggers are commonly used to enforce business rules, maintain data integrity, audit data changes, or perform other complex operations.

Types of Triggers

SQL Server supports several types of triggers:

DML Triggers: These fire in response to INSERT, UPDATE, or DELETE operations on a table or view. They can be configured to fire AFTER or INSTEAD OF the triggering event.
DDL Triggers: These fire in response to DDL events such as CREATE, ALTER, or DROP statements. They are useful for auditing DDL changes or enforcing database schema rules.
Logon Triggers: These fire in response to a logon event. They can be used to manage user sessions and audit logon activities.

DML Triggers in Detail

DML triggers are the most common type. They are associated with a specific table or view and execute when a DML statement affects that object.

`AFTER` Triggers

An AFTER trigger executes after the triggering DML statement has completed. This is the default behavior if no type is specified. You can access the inserted and deleted virtual tables within an AFTER trigger to see the rows that were affected by the DML operation.

`INSTEAD OF` Triggers

An INSTEAD OF trigger executes instead of the triggering DML statement. This is particularly useful for updating views that cannot be directly updated by the system. Similar to AFTER triggers, INSTEAD OF triggers can use the inserted and deleted tables to examine the proposed changes.

Creating a DML Trigger

The basic syntax for creating a DML trigger is as follows:

CREATE TRIGGER TriggerName ON TableName AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- Trigger logic goes here
PRINT 'Trigger executed.'
END
GO

You can specify which DML events the trigger should respond to (e.g., INSERT, UPDATE, DELETE).

Example: Audit Trigger

This example demonstrates an AFTER INSERT trigger that logs changes to a Products table into an audit table.

CREATE TRIGGER TR_Products_AuditInsert ON Products AFTER INSERT
AS
BEGIN
    -- Prevent trigger recursion if the audit table itself is modified
    IF TRIGGER_NESTLEVEL() > 1 RETURN

    -- Insert affected rows into the audit table
    INSERT INTO AuditLog (
        LogMessage,
        Operation,
        TableName,
        RecordID,
        OperationTime
    )
    SELECT
        'New product inserted',
        'INSERT',
        'Products',
        i.ProductID,
        GETDATE()
    FROM inserted i;
END
GO

`inserted` and `deleted` Tables

Within a DML trigger, SQL Server creates two temporary, in-memory tables named inserted and deleted. These tables contain copies of the rows affected by the DML statement.

The inserted table contains the new rows for INSERT and UPDATE operations.
The deleted table contains the old rows for DELETE and UPDATE operations.

These tables are crucial for determining what data was changed and for implementing complex logic based on those changes.

Managing Triggers

Enabling/Disabling: Triggers can be enabled or disabled using ALTER TRIGGER ... ENABLE or ALTER TRIGGER ... DISABLE.
Dropping: Triggers can be removed from the database using DROP TRIGGER TriggerName.

Considerations for Triggers

Performance: Triggers add overhead to DML operations. Complex triggers can significantly impact performance. Always test the performance implications.
Recursion: Triggers can cause recursive calls if a trigger modifies a table that has another trigger defined on it. Use TRIGGER_NESTLEVEL() to prevent infinite recursion.
Error Handling: Implement robust error handling within triggers to manage unexpected situations gracefully.
Readability: Keep trigger logic as simple and readable as possible. Complex business logic is often better placed in stored procedures or application code.

For more in-depth information, refer to the official T-SQL Statements documentation.