Microsoft SQL Server Documentation

Triggers (Transact-SQL)

A trigger is a special type of stored procedure that automatically executes or fires when an event occurs in the database server. Triggers are used to enforce complex business rules, maintain data integrity, and implement auditing or logging mechanisms.

Triggers can be defined to fire for Data Manipulation Language (DML) events (INSERT, UPDATE, DELETE) or Data Definition Language (DDL) events (CREATE, ALTER, DROP). They can also be used for LOGON events.

Types of Triggers

• DML Triggers: These triggers fire in response to INSERT, UPDATE, or DELETE statements executed against a table or view.
• DDL Triggers: These triggers fire in response to DDL statements that modify the database schema.
• LOGON Triggers: These triggers fire in response to a LOGON event, which occurs when a user session is established.

DML Triggers

DML triggers are the most common type. They can be defined to execute either AFTER the DML statement or INSTEAD OF the DML statement.

AFTER Triggers

AFTER triggers execute after the DML statement has completed. They are useful for performing actions that depend on the success of the original statement, such as updating related tables or logging changes.

AFTER triggers can be defined as FOR EACH ROW (which is the default in SQL Server) or FOR EACH STATEMENT. In SQL Server, DML triggers always fire once per statement, not once per row, though they have access to the inserted and deleted logical tables, which can contain multiple rows.

INSTEAD OF Triggers

INSTEAD OF triggers execute in place of the DML statement. They are particularly useful for implementing complex logic on views that cannot be directly modified by DML statements.

DDL Triggers

DDL triggers are fired in response to various DDL events. They are useful for auditing schema changes, preventing certain schema modifications, or implementing custom security policies.

DDL triggers can be defined to respond to specific DDL events or to a set of events using the EVENT_GROUP parameter.

LOGON Triggers

LOGON triggers are executed when a user logs in to a SQL Server instance. They can be used to audit login events, enforce session-level settings, or restrict access based on certain conditions.

Creating a Trigger

The basic syntax for creating a trigger is as follows:

CREATE TRIGGER trigger_name
ON table_or_view_name
[ WITH ENCRYPTION ]
[ FOR | AFTER | INSTEAD OF ] { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH EXECUTE AS { self | 'user_name' | owner } ]
[ NOT FOR REPLICATION ]
AS
BEGIN
    -- Trigger logic goes here
    -- Use the inserted and deleted logical tables for DML triggers
    IF EXISTS (
        SELECT *
        FROM inserted
        WHERE some_condition
    )
    BEGIN
        -- Perform actions
    END
END;

The `inserted` and `deleted` Logical Tables

For DML triggers, SQL Server maintains two special logical tables: inserted and deleted. These tables contain copies of the rows affected by the triggering DML statement.

• inserted: Contains the new rows after an INSERT or UPDATE operation. For UPDATE, it contains the row after the update.
• deleted: Contains the old rows before a DELETE or UPDATE operation. For UPDATE, it contains the row before the update.

These tables are crucial for comparing old and new data, validating changes, and implementing complex business logic.

Example: Auditing Product Price Changes

This example creates an AFTER UPDATE trigger on the Products table to log changes to the UnitPrice column into an AuditProductPrices table.

CREATE TABLE AuditProductPrices (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    ProductID INT,
    OldPrice DECIMAL(10, 2),
    NewPrice DECIMAL(10, 2),
    ChangeDate DATETIME DEFAULT GETDATE()
);
GO

CREATE TRIGGER trg_AuditProductPriceChanges
ON Products
AFTER UPDATE OF UnitPrice
AS
BEGIN
    SET NOCOUNT ON;

    IF UPDATE(UnitPrice) AND EXISTS (SELECT * FROM inserted)
    BEGIN
        INSERT INTO AuditProductPrices (ProductID, OldPrice, NewPrice)
        SELECT
            i.ProductID,
            d.UnitPrice, -- Old price from deleted table
            i.UnitPrice  -- New price from inserted table
        FROM
            inserted i
        INNER JOIN
            deleted d ON i.ProductID = d.ProductID
        WHERE i.UnitPrice <> d.UnitPrice; -- Only log if price actually changed
    END
END;
GO

Managing Triggers

You can view existing triggers, modify them, or drop them using the following system stored procedures and statements:

• sp_helptext 'trigger_name': To view the trigger's definition.
• ALTER TRIGGER trigger_name ON ...: To modify an existing trigger.
• DROP TRIGGER trigger_name: To remove a trigger.

Understanding and effectively using triggers is a key aspect of advanced database development with SQL Server, allowing for robust data management and business logic implementation.