After Trigger, Instead of Trigger Example

Estimated study time: 30 minutes. See both trigger types act on the same INSERT.

The clearest way to understand AFTER and INSTEAD OF triggers is to watch them handle the exact same action differently. Below, both triggers respond to an INSERT into an Orders table.

Setting Up the Table

CREATE TABLE Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    ProductName VARCHAR(100),
    Quantity INT,
    OrderDate DATETIME DEFAULT GETDATE()
);

Example 1: AFTER INSERT Trigger

An AFTER trigger lets the insert happen first, then runs extra logic - here, logging the new order into an audit table.

CREATE TABLE OrderAudit (
    AuditId INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT,
    LoggedAt DATETIME DEFAULT GETDATE()
);
GO

CREATE TRIGGER trg_AfterOrderInsert
ON Orders
AFTER INSERT
AS
BEGIN
    INSERT INTO OrderAudit(OrderId)
    SELECT OrderId FROM inserted;
END;
GO

INSERT INTO Orders(ProductName, Quantity) VALUES ('Keyboard', 2);
-- Row is inserted into Orders AND a matching row appears in OrderAudit

Example 2: INSTEAD OF INSERT Trigger

An INSTEAD OF trigger takes over completely - the original INSERT never touches the table unless the trigger explicitly performs it. This is useful for validation before a row is allowed in.

CREATE TRIGGER trg_InsteadOfOrderInsert
ON Orders
INSTEAD OF INSERT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE Quantity <= 0)
    BEGIN
        PRINT 'Quantity must be greater than zero. Insert blocked.';
        RETURN;
    END

    INSERT INTO Orders(ProductName, Quantity, OrderDate)
    SELECT ProductName, Quantity, GETDATE() FROM inserted;
END;
GO

INSERT INTO Orders(ProductName, Quantity) VALUES ('Monitor', 0);
-- Blocked: Quantity must be greater than zero. Insert blocked.

INSERT INTO Orders(ProductName, Quantity) VALUES ('Monitor', 1);
-- Succeeds: row is inserted through the trigger's own INSERT statement

Key Difference

In the AFTER example, the row lands in Orders automatically, and the trigger only adds a side effect afterward. In the INSTEAD OF example, nothing reaches Orders unless the trigger's own logic writes it there - which is exactly what lets it reject bad data before it's ever stored.