Triggers

Also used for soft deletes: INSTEAD OF DELETE → UPDATE ...

21.1 DML Triggers (AFTER / FOR)

inserted holds the new rows (INSERT/UPDATE). deleted holds the old rows (UPDATE/DELETE).

Triggers fire once per statement, not per row. Always write set-based trigger code.

UPDATE(column) and COLUMNS_UPDATED() detect which columns were targeted.

CREATE OR ALTER TRIGGER dbo.trg_Products_Audit
ON dbo.Products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted) RETURN;

    INSERT dbo.ProductAudit (ProductID, Action, OldPrice, NewPrice, ChangedBy, ChangedAt)
    SELECT COALESCE(i.ProductID, d.ProductID),
           CASE WHEN i.ProductID IS NOT NULL AND d.ProductID IS NOT NULL THEN 'U'
                WHEN i.ProductID IS NOT NULL THEN 'I' ELSE 'D' END,
           d.UnitPrice, i.UnitPrice, SUSER_SNAME(), SYSDATETIME()
    FROM inserted i
    FULL JOIN deleted d ON d.ProductID = i.ProductID;
END

21.2 INSTEAD OF Triggers

Also used for soft deletes: INSTEAD OF DELETE → UPDATE ... SET IsDeleted = 1.

CREATE TRIGGER dbo.trg_vwCustomerOrders_Insert ON Sales.vw_CustomerOrders
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- custom logic to insert into the base tables
END

21.3 DDL Triggers

CREATE TRIGGER trg_PreventDropTable ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
    DECLARE @e XML = EVENTDATA();
    PRINT 'Schema changes are not allowed: ' + @e.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
    ROLLBACK;
END

21.4 Logon Triggers

A buggy logon trigger can lock everyone out. Connect through the DAC (ADMIN:server) to fix it.

CREATE TRIGGER trg_LimitConnections ON ALL SERVER FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'ReportUser'
       AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE original_login_name = 'ReportUser') > 5
        ROLLBACK;
END

21.5 Trigger Management

DISABLE TRIGGER dbo.trg_Products_Audit ON dbo.Products;
ENABLE TRIGGER ALL ON dbo.Products;
EXEC sp_settriggerorder @triggername = 'dbo.trg_Products_Audit', @order = 'First', @stmttype = 'UPDATE';
-- Nested triggers (server option), recursive triggers (database option), TRIGGER_NESTLEVEL()

21.6 Trigger Best Practices

Keep triggers small and fast. They run inside the caller's transaction.

Avoid business logic hidden in triggers. Consider temporal tables, CDC, or the OUTPUT clause instead.