SQL Triggers

This section provides detailed information about SQL Server triggers, including their purpose, types, syntax, and best practices.

What is a SQL 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 modification language (DML) statements such as INSERT, UPDATE, or DELETE on a specific table. Triggers can also be fired by data definition language (DDL) statements or database-level events.

Types of Triggers

SQL Server supports several types of triggers:

• DML Triggers: Fire in response to INSERT, UPDATE, or DELETE operations on a table or view.
• DDL Triggers: Fire in response to DDL events such as CREATE, ALTER, or DROP statements.
• Logon Triggers: Fire in response to logon events, which can be used for auditing or controlling user access.

DML Triggers

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

• AFTER Triggers: Execute after the DML statement that fired them has completed.
• INSTEAD OF Triggers: Execute instead of the DML statement. These are particularly useful for maintaining views that cannot be directly updated.

Creating an AFTER Trigger

The basic syntax for creating an AFTER trigger is:

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- Trigger logic goes here
    -- Use the inserted and deleted pseudo-tables
END;

Inside a DML trigger, you can access two special tables:

• inserted: Contains the new rows or the updated values.
• deleted: Contains the old rows or the values before the modification.

Example: Audit Trigger

This trigger logs changes to the Products table into an audit table.

CREATE TRIGGER trg_ProductAudit
ON Products
AFTER UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN -- UPDATE operation
        INSERT INTO ProductAuditLog (ProductID, OldPrice, NewPrice, ChangeDate, ChangeType)
        SELECT
            d.ProductID,
            d.Price,
            i.Price,
            GETDATE(),
            'UPDATE'
        FROM deleted d
        JOIN inserted i ON d.ProductID = i.ProductID;
    END
    ELSE IF EXISTS (SELECT * FROM deleted)
    BEGIN -- DELETE operation
        INSERT INTO ProductAuditLog (ProductID, OldPrice, NewPrice, ChangeDate, ChangeType)
        SELECT
            d.ProductID,
            d.Price,
            NULL,
            GETDATE(),
            'DELETE'
        FROM deleted d;
    END
END;

Creating an INSTEAD OF Trigger

The syntax for an INSTEAD OF trigger is similar:

CREATE TRIGGER trigger_name
ON table_or_view_name
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
    -- Trigger logic to perform the action instead of the original DML statement
END;

Example: INSTEAD OF Trigger for a View

Consider a view that combines data from multiple tables. An INSTEAD OF trigger can handle INSERT operations on this view.

-- Assuming View 'vw_CustomerOrderDetails' combines Customer and Order info
CREATE TRIGGER trg_vw_CustomerOrderDetails_Insert
ON vw_CustomerOrderDetails
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert into Customers table if customer doesn't exist
    INSERT INTO Customers (CustomerID, FirstName, LastName)
    SELECT i.CustomerID, i.FirstName, i.LastName
    FROM inserted i
    LEFT JOIN Customers c ON i.CustomerID = c.CustomerID
    WHERE c.CustomerID IS NULL;

    -- Insert into Orders table
    INSERT INTO Orders (OrderID, CustomerID, OrderDate)
    SELECT i.OrderID, i.CustomerID, GETDATE()
    FROM inserted i;
END;

DML Trigger Considerations

• FOR EACH ROW vs. FOR EACH STATEMENT: SQL Server triggers are statement-level. The trigger logic executes once per statement, not once per row affected. You must handle multiple rows within the trigger logic using the inserted and deleted tables.
• Recursion: Triggers can be configured to be reentrant or to prevent recursion. Be cautious of triggers that might fire themselves indirectly.
• Performance: Complex or inefficient triggers can significantly impact database performance. Test triggers thoroughly under load.
• Order of Execution: If multiple triggers exist on the same table and event, their execution order is not guaranteed unless explicitly controlled using system stored procedures like sp_settriggerorder.

DDL Triggers

DDL triggers respond to events like CREATE TABLE, ALTER VIEW, DROP DATABASE, etc. They are useful for auditing DDL changes or enforcing DDL policies.

CREATE TRIGGER trg_DDL_PreventDropTable
ON DATABASE
AFTER DROP_TABLE
AS
BEGIN
    PRINT 'Dropping tables is not allowed by this trigger.';
    ROLLBACK; -- Revert the DROP TABLE operation
END;

Best Practices

• Keep triggers small and focused.
• Avoid performing complex operations within triggers that could cause deadlocks or performance issues.
• Use SET NOCOUNT ON;.
• Handle multi-row operations correctly.
• Document your triggers thoroughly.
• Consider alternatives like stored procedures or application logic when possible.