Triggers

What Is a Trigger?

A trigger is a special stored procedure that SQL Server executes automatically whenever a specific data event occurs on a table or view. You do not call a trigger manually — it fires in response to an INSERT, UPDATE, or DELETE statement, running as part of the same transaction. This makes triggers powerful for enforcing business rules that cannot be expressed as constraints, maintaining audit trails, and keeping derived data synchronised without requiring application code to handle it.

SQL Server supports two timing models. A AFTER trigger (also called a FOR trigger) fires after the data modification has completed and the row has been written. An INSTEAD OF trigger fires in place of the data modification — the trigger body decides whether to carry out the change at all, and how. AFTER triggers are the most common type. INSTEAD OF triggers are used primarily on views to make non-updatable views writable.

• Triggers fire automatically — they cannot be called directly like a stored procedure
• AFTER triggers run after the INSERT, UPDATE, or DELETE has completed
• INSTEAD OF triggers replace the data modification — the trigger controls what actually happens
• Every trigger has access to two virtual tables: inserted and deleted
• inserted holds the new row values — available in INSERT and UPDATE triggers
• deleted holds the old row values — available in DELETE and UPDATE triggers
• Triggers run inside the same transaction as the statement that fired them — a ROLLBACK inside the trigger rolls back everything

-- AFTER INSERT trigger: log every new order into an audit table.
-- The 'inserted' virtual table contains the rows just added to Orders.
-- First create the audit table to receive the log entries
CREATE TABLE OrderAudit (
    AuditId     INT           IDENTITY PRIMARY KEY,
    OrderId     INT           NOT NULL,
    UserId      INT           NOT NULL,
    Total       DECIMAL(10,2) NOT NULL,
    Action      NVARCHAR(10)  NOT NULL,   -- 'INSERT', 'UPDATE', or 'DELETE'
    ActionDate  DATETIME2     NOT NULL DEFAULT GETDATE(),
    ActionBy    NVARCHAR(128) NOT NULL DEFAULT SYSTEM_USER
);
-- Create the trigger on the Orders table
CREATE TRIGGER trg_Orders_AfterInsert
ON dbo.Orders
AFTER INSERT                              -- fires after every INSERT on Orders
AS
BEGIN
    SET NOCOUNT ON;                       -- suppress row-count messages from inside trigger
    INSERT INTO dbo.OrderAudit (OrderId, UserId, Total, Action)
    SELECT
        i.OrderId,
        i.UserId,
        i.Total,
        'INSERT'
    FROM inserted i;                      -- 'inserted' contains the newly added rows
END;

• The trigger fires automatically the moment the INSERT on Orders completes — no application code needed
• SET NOCOUNT ON suppresses the extra "1 row(s) affected" message that the INSERT inside the trigger would otherwise send back to the caller
• The inserted table can contain multiple rows if the original INSERT statement added more than one row — the trigger SELECT handles all of them at once
• SYSTEM_USER captures the SQL Server login that executed the INSERT — useful for accountability in audit trails

AFTER UPDATE Trigger — The inserted and deleted Tables

An UPDATE trigger has access to both inserted (the new values) and deleted (the old values). Joining them on the primary key gives you a before-and-after view of every changed row in a single result set. This is the standard pattern for change tracking — capture what the row looked like before the update and what it looks like after, then log or act on the difference.

-- AFTER UPDATE trigger: detect price changes on Products and log the before/after.
-- JOIN inserted and deleted on the PK to see what changed on each row.
CREATE TRIGGER trg_Products_AfterUpdate
ON dbo.Products
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- only act if the Price column was actually changed
    -- IF UPDATE(Price) returns true when Price is included in the SET clause
    IF UPDATE(Price)
    BEGIN
        INSERT INTO dbo.PriceChangeLog
            (ProductId, OldPrice, NewPrice, ChangedDate, ChangedBy)
        SELECT
            i.ProductId,
            d.Price     AS OldPrice,    -- deleted = row before the update
            i.Price     AS NewPrice,    -- inserted = row after the update
            GETDATE(),
            SYSTEM_USER
        FROM inserted i
        JOIN deleted  d ON i.ProductId = d.ProductId  -- match old and new by PK
        WHERE i.Price <> d.Price;      -- only rows where Price genuinely changed
    END;
END;
-- Trigger in action:
UPDATE dbo.Products SET Price = 319.99 WHERE ProductId = 5;  -- Monitor 27-inch
-- Check the log:
SELECT * FROM PriceChangeLog WHERE ProductId = 5;

• IF UPDATE(Price) checks whether the Price column was included in the SET clause — it prevents the trigger body from running unnecessarily on updates to other columns
• Joining inserted and deleted on ProductId aligns the before and after values for each updated row
• WHERE i.Price <> d.Price guards against no-op updates where the value is set to the same number it already held
• For multi-row UPDATEs both inserted and deleted contain multiple rows — the JOIN handles all of them correctly

AFTER DELETE Trigger

A DELETE trigger has access only to deleted — the rows that were just removed. The inserted table is empty for a DELETE trigger. The most common use is a soft-delete pattern: rather than allowing the row to be permanently gone, the trigger copies it to an archive table before the transaction completes, or raises an error to prevent the deletion entirely under certain conditions.

-- AFTER DELETE trigger: archive deleted orders before they disappear.
-- 'deleted' holds the rows that were just removed from Orders.
CREATE TRIGGER trg_Orders_AfterDelete
ON dbo.Orders
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.OrdersArchive
        (OrderId, UserId, Total, OrderDate, Status, DeletedDate, DeletedBy)
    SELECT
        d.OrderId,
        d.UserId,
        d.Total,
        d.OrderDate,
        d.Status,
        GETDATE(),
        SYSTEM_USER
    FROM deleted d;    -- deleted contains every row removed by the DELETE statement
END;
-- Trigger that PREVENTS deletion of shipped or delivered orders:
CREATE TRIGGER trg_Orders_PreventDelete
ON dbo.Orders
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (
        SELECT 1 FROM deleted
        WHERE Status IN ('shipped', 'delivered')  -- check the removed rows
    )
    BEGIN
        RAISERROR('Cannot delete shipped or delivered orders.', 16, 1);
        ROLLBACK TRANSACTION;   -- rolls back the DELETE and the trigger together
    END;
END;

• deleted holds the rows being removed — they are already gone from Orders when the trigger runs but still fully accessible through this virtual table
• RAISERROR followed by ROLLBACK TRANSACTION aborts the DELETE and rolls back all changes in the current transaction, including any work the trigger itself did
• Two AFTER DELETE triggers exist on Orders — SQL Server fires them in an unspecified order; use sp_settriggerorder if the sequence matters
• The prevention trigger checks the deleted table, not the Orders table — by the time AFTER triggers run the rows are already removed from the base table

INSTEAD OF Trigger — Controlling Updates on a View

An INSTEAD OF trigger intercepts the data modification before it reaches the underlying table and replaces it entirely. The trigger body decides what actually happens — it can reroute the change to different tables, apply transformations, enforce custom rules, or perform nothing at all. INSTEAD OF triggers are the standard mechanism for making views over multiple tables behave as if they were a single writable table.

-- View joining Users and UserProfiles -- not directly updatable without a trigger.
CREATE VIEW vw_CustomerSummary AS
SELECT
    u.UserId,
    u.FirstName,
    u.LastName,
    u.Email,
    up.Bio,
    up.AvatarUrl
FROM dbo.Users        u
LEFT JOIN dbo.UserProfiles up ON u.UserId = up.UserId;
-- INSTEAD OF UPDATE: intercept updates to the view and route them
-- to the correct underlying tables.
CREATE TRIGGER trg_CustomerSummary_InsteadOfUpdate
ON dbo.vw_CustomerSummary
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- update Users table with name/email changes
    UPDATE u
    SET u.FirstName = i.FirstName,
        u.LastName  = i.LastName,
        u.Email     = i.Email
    FROM dbo.Users u
    JOIN inserted  i ON u.UserId = i.UserId;
    -- update UserProfiles with bio/avatar changes (insert if profile doesn't exist)
    MERGE dbo.UserProfiles AS target
    USING inserted          AS source ON target.UserId = source.UserId
    WHEN MATCHED THEN
        UPDATE SET Bio = source.Bio, AvatarUrl = source.AvatarUrl
    WHEN NOT MATCHED THEN
        INSERT (UserId, Bio, AvatarUrl, JoinDate)
        VALUES (source.UserId, source.Bio, source.AvatarUrl, CAST(GETDATE() AS DATE));
END;

• The UPDATE on the view never reaches the underlying tables directly — the INSTEAD OF trigger intercepts it entirely
• The trigger uses inserted to access the new values the caller wanted to write, then routes each column to the correct table
• MERGE inside the trigger handles the case where a UserProfiles row does not yet exist — it inserts one rather than failing
• INSTEAD OF triggers are the cleanest way to make complex views fully updatable without exposing the underlying schema to the caller

Managing and Disabling Triggers

Triggers are database objects that can be listed, disabled, re-enabled, and dropped using DDL statements. Disabling a trigger does not remove it — it simply stops it from firing until it is re-enabled. This is useful during bulk data loads where trigger overhead would be significant, or during debugging to isolate whether a trigger is causing unexpected behaviour. The system catalogue view sys.triggers lists every trigger in the database along with its parent object and current enabled state.

-- Disable a trigger temporarily (e.g. during a bulk load)
DISABLE TRIGGER trg_Orders_AfterInsert ON dbo.Orders;
-- Re-enable it once the bulk load is complete
ENABLE TRIGGER trg_Orders_AfterInsert ON dbo.Orders;
-- Drop a trigger permanently
DROP TRIGGER IF EXISTS trg_Orders_AfterInsert;
-- List all triggers in the database with their parent table and status
SELECT
    t.name                          AS TriggerName,
    OBJECT_NAME(t.parent_id)        AS TableName,
    t.type_desc                     AS TriggerType,
    t.is_disabled                   AS IsDisabled,
    t.create_date                   AS Created,
    t.modify_date                   AS LastModified
FROM sys.triggers t
WHERE t.parent_class = 1           -- 1 = table triggers (not database-level triggers)
ORDER BY TableName, TriggerName;

• DISABLE TRIGGER and ENABLE TRIGGER require the table name — unlike DROP, they do not work with just the trigger name alone
• is_disabled = 1 confirms trg_Orders_AfterInsert is currently off — no audit rows will be written during the bulk load period
• DROP TRIGGER IF EXISTS is the safe drop syntax — it does not error if the trigger does not exist
• Use a consistent naming convention for triggers — the trg_TableName_EventType pattern makes the catalogue easy to scan at a glance

Lesson Summary

Practice Questions

Quiz

Next up — SQL Server Security and Roles — How logins, users, roles, and permissions control who can access and modify data in your database.