SQL Triggers

A trigger is a special type of stored procedure that automatically runs or executes when an event occurs in the database. This event can be a DML (Data Manipulation Language) event like INSERT, UPDATE, or DELETE on a particular table, or a DDL (Data Definition Language) event like CREATE, ALTER, or DROP on any database object.

Types of Triggers

SQL Server supports several types of triggers:

• After Triggers: These triggers fire after the triggering event has completed. They are useful for enforcing business rules, maintaining data integrity, or logging changes.
• Instead Of Triggers: These triggers fire instead of the triggering event. They are often used to create updatable views on complex data structures or to prevent certain actions from occurring.

DML Triggers

DML triggers are associated with DML statements (INSERT, UPDATE, DELETE) on a table or view.

Creating a DML Trigger

The basic syntax for creating an AFTER INSERT trigger is:

CREATE TRIGGER trigger_name
ON table_name
AFTER INSERT
AS
BEGIN
    -- Trigger logic here
    PRINT 'Record inserted!';
END;

Similarly, you can create triggers for UPDATE and DELETE events:

CREATE TRIGGER update_trigger_name
ON table_name
AFTER UPDATE
AS
BEGIN
    -- Logic for update event
END;

CREATE TRIGGER delete_trigger_name
ON table_name
AFTER DELETE
AS
BEGIN
    -- Logic for delete event
END;

The inserted and deleted Tables

Inside a DML trigger, you have access to two special virtual tables: inserted and deleted. These tables contain rows that are affected by the DML statement that fired the trigger.

• inserted: Contains the new rows or the updated values after an INSERT or UPDATE operation.
• deleted: Contains the old rows or the values before an UPDATE or DELETE operation.

Example: Logging Changes

Here's an example of a trigger that logs changes to an Employees table into an EmployeeAudit table:

CREATE TABLE EmployeeAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    ChangeType VARCHAR(10),
    ChangeTimestamp DATETIME DEFAULT GETDATE()
);

GO

CREATE TRIGGER trg_EmployeeAudit
ON Employees
AFTER UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
    BEGIN
        -- Handle UPDATE operation
        INSERT INTO EmployeeAudit (EmployeeID, ChangeType)
        SELECT
            i.EmployeeID,
            'UPDATE'
        FROM inserted i;
    END
    ELSE IF EXISTS(SELECT * FROM deleted)
    BEGIN
        -- Handle DELETE operation
        INSERT INTO EmployeeAudit (EmployeeID, ChangeType)
        SELECT
            d.EmployeeID,
            'DELETE'
        FROM deleted d;
    END
END;

DDL Triggers

DDL triggers are associated with DDL events. They fire in response to statements that create, alter, or drop database objects.

Creating a DDL Trigger

The syntax for a DDL trigger uses event data functions to get information about the DDL event.

CREATE TRIGGER trg_PreventTableDrop
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'Table drop operations are not allowed!';
    ROLLBACK TRANSACTION;
END;

This trigger prevents any table from being dropped within the database.

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the triggering DML statement. They are particularly useful for making complex views updatable.

Creating an INSTEAD OF Trigger

CREATE TRIGGER trg_UpdateCustomerView
ON CustomerView
INSTEAD OF INSERT
AS
BEGIN
    -- Logic to insert into underlying tables
    INSERT INTO Customers (CustomerID, Name)
    SELECT CustomerID, Name FROM inserted;
END;

Trigger Performance Considerations

• Avoid complex operations within triggers that can slow down DML statements.
• Minimize the use of cursors within triggers; set-based operations are generally more efficient.
• Test trigger performance thoroughly under load.
• Consider using SET NOCOUNT ON to prevent sending "rows affected" messages back to the client, which can improve performance.