SQL Server Trigger After Insert (and After Insert, Update)

I inserted three orders in one statement, and my audit trigger logged one of them. No error. No warning.

A SQL Server trigger after insert runs once per INSERT statement, after the rows land, and sees every new row in the inserted pseudo-table. Read it as a set and the missing rows come back.

Here’s what we’re building: an audit trigger for new orders and Status changes, plus a trigger that queues orders over $500. The final test run:

Before You Start: Test Setup and the Sample Orders Table

Test setup: Microsoft SQL Server 2025 (RTM) - 17.0.1000.7 (X64), Standard Developer Edition, sqlcmd in the VS Code terminal, TriggerAfterInsertDemo.

If you’re new to triggers, read what a trigger is in SQL Server. Every step reuses these tables:

CREATE TABLE dbo.Orders (
    OrderID  int           PRIMARY KEY,
    Customer nvarchar(16)  NOT NULL,
    State    char(2)       NOT NULL,
    Amount   decimal(10,2) NOT NULL,
    Status   nvarchar(8)   NOT NULL DEFAULT N'New'
);
CREATE TABLE dbo.OrderAudit (
    AuditID  int IDENTITY  PRIMARY KEY,
    OrderID  int           NOT NULL,
    Action   char(6)       NOT NULL,
    Amount   decimal(10,2) NULL
);
CREATE TABLE dbo.ReviewQueue (
    Customer     nvarchar(16) PRIMARY KEY,
    FirstOrderID int          NOT NULL
);
INSERT INTO dbo.Orders (OrderID, Customer, State, Amount) VALUES
(1001, N'Megan Bowen', 'WA',  245.50),
(1002, N'Alex Wilber', 'TX',   89.99),
(1003, N'Adele Vance', 'CA', 1210.00);
INSERT INTO dbo.ReviewQueue VALUES (N'Adele Vance', 1003);

Step 1: Create the AFTER INSERT Trigger the Classic Way

Older examples, this page’s included, copy one row of inserted into variables. FOR INSERT and AFTER INSERT mean the same thing:

CREATE TRIGGER dbo.trg_Orders_Audit
ON dbo.Orders
FOR INSERT                      -- FOR = AFTER
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @OrderID int;
    SELECT @OrderID = OrderID FROM inserted;
    INSERT INTO dbo.OrderAudit (OrderID, Action)
    VALUES (@OrderID, 'INSERT');
END;
GO
SET NOCOUNT ON;
INSERT INTO dbo.Orders (OrderID, Customer, State, Amount) VALUES
(1004, N'Lee Gu',       'IL', 158.00),
(1005, N'Nestor Wilke', 'FL', 990.40),
(1006, N'Joni Sherman', 'OH',  67.20);
SELECT OrderID, Action FROM dbo.OrderAudit;

Output:

OrderID     Action
----------- ------
       1004 INSERT

What I expected vs what I saw: three audit rows expected, one logged. The variables keep whichever row SQL Server read last.

Checkpoint: six orders, one audit row. You’ll hit this again when getting the inserted value in a trigger.

Step 2: See the Subquery Trigger Fail With Msg 512

With a subquery, SQL Server refuses out loud. I caught it with TRY…CATCH:

CREATE OR ALTER TRIGGER dbo.trg_Orders_Audit
ON dbo.Orders
AFTER INSERT
AS
    INSERT INTO dbo.OrderAudit (OrderID, Action)
    VALUES ((SELECT OrderID FROM inserted), 'INSERT');
GO
SET NOCOUNT ON;
BEGIN TRY
    INSERT INTO dbo.Orders (OrderID, Customer, State, Amount) VALUES
    (1004, N'Lee Gu', 'IL', 158.00), (1005, N'Nestor Wilke', 'FL', 990.40);
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;
SELECT COUNT(*) AS Orders FROM dbo.Orders;

Output:

Msg 512: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Orders     
-----------
          3

Important: the INSERT rolled back, so three orders remain.

Step 3: Rewrite the Trigger Set-Based With CREATE OR ALTER TRIGGER

One INSERT ... SELECT FROM inserted handles one row or 10,000. CREATE OR ALTER needs SQL Server 2016 SP1 or later:

CREATE OR ALTER TRIGGER dbo.trg_Orders_Audit
ON dbo.Orders
AFTER INSERT
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;   -- before any SET statement
    SET NOCOUNT ON;
    INSERT INTO dbo.OrderAudit (OrderID, Action, Amount)
    SELECT OrderID, 'INSERT', Amount
    FROM inserted;
END;
GO
SET NOCOUNT ON;
INSERT INTO dbo.Orders (OrderID, Customer, State, Amount) VALUES
(1004, N'Lee Gu',       'IL', 158.00),
(1005, N'Nestor Wilke', 'FL', 990.40),
(1006, N'Joni Sherman', 'OH',  67.20);
SELECT OrderID, Action FROM dbo.OrderAudit;

Output:

OrderID     Action
----------- ------
       1006 INSERT
       1005 INSERT
       1004 INSERT

Checkpoint: three audit rows. What this tells me: the trigger fired once, and inserted held all three rows.

Step 4: Add a Trigger Condition (Over $500, If Not Exists)

The old “insert if not exists” idea, set-based, as a second AFTER trigger on the same table:

CREATE OR ALTER TRIGGER dbo.trg_Orders_Review
ON dbo.Orders
AFTER INSERT
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;
    SET NOCOUNT ON;
    INSERT INTO dbo.ReviewQueue (Customer, FirstOrderID)
    SELECT i.Customer, MIN(i.OrderID)
    FROM inserted AS i
    WHERE i.Amount > 500
      AND NOT EXISTS (SELECT 1 FROM dbo.ReviewQueue AS q
                      WHERE q.Customer = i.Customer)
    GROUP BY i.Customer;
END;

Checkpoint: sqlcmd prints nothing; the test below proves it. More on inserting into another table from a trigger.

Step 5: Handle AFTER INSERT, UPDATE in One Trigger

On an INSERT, deleted is empty; on an UPDATE it holds the old rows. UPDATE(Status) is true when Status is in the SET list:

CREATE OR ALTER TRIGGER dbo.trg_Orders_Audit
ON dbo.Orders
AFTER INSERT, UPDATE
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM deleted) AND NOT UPDATE(Status)
        RETURN;
    INSERT INTO dbo.OrderAudit (OrderID, Action, Amount)
    SELECT OrderID,
           IIF(EXISTS (SELECT 1 FROM deleted), 'UPDATE', 'INSERT'),
           Amount
    FROM inserted;
END;
GO
SET NOCOUNT ON;
UPDATE dbo.Orders SET Amount = 99.99 WHERE OrderID = 1002;
UPDATE dbo.Orders SET Status = N'Shipped' WHERE OrderID = 1001;
SELECT OrderID, Action FROM dbo.OrderAudit;

Output:

OrderID     Action
----------- ------
       1001 UPDATE

Amount changes are ignored. Add DELETE and rows only in deleted mean a delete (trigger for delete); to compare values, see firing only when a column is modified.

Test the Audit Trigger With a Multi-Row Insert and an Update

Both triggers on. Three orders, then Megan’s ships:

SET NOCOUNT ON;
INSERT INTO dbo.Orders (OrderID, Customer, State, Amount) VALUES
(1004, N'Lee Gu',      'IL',  158.00),
(1005, N'Adele Vance', 'CA',  640.00),
(1006, N'Megan Bowen', 'WA', 1450.00);
UPDATE dbo.Orders SET Status = N'Shipped' WHERE OrderID = 1006;
SELECT OrderID, Action FROM dbo.OrderAudit;
SELECT Customer, FirstOrderID FROM dbo.ReviewQueue;

Output:

OrderID     Action
----------- ------
       1006 INSERT
       1005 INSERT
       1004 INSERT
       1006 UPDATE
Customer         FirstOrderID
---------------- ------------
Adele Vance              1003
Megan Bowen              1006

Four audit rows. Megan is queued, Adele already was, and Lee’s $158 doesn’t qualify. If you see something else, debug the SQL Server trigger before changing it.

Why Does OUTPUT Fail With Msg 334 on a Table With a Trigger?

OUTPUT without INTO fails on a table with an enabled trigger. It’s a compile error, so my first TRY…CATCH missed it. Dynamic SQL fixed that:

SET NOCOUNT ON;
BEGIN TRY
    EXEC sys.sp_executesql N'INSERT INTO dbo.Orders
        (OrderID, Customer, State, Amount) OUTPUT inserted.OrderID
        VALUES (1004, N''Lee Gu'', ''IL'', 158);';
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;
DECLARE @New TABLE (OrderID int);
INSERT INTO dbo.Orders (OrderID, Customer, State, Amount)
OUTPUT inserted.OrderID INTO @New
VALUES (1004, N'Lee Gu', 'IL', 158.00);
SELECT OrderID AS NewOrderID FROM @New;

Output:

Msg 334: The target table 'dbo.Orders' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
NewOrderID 
-----------
       1004

It’s also how I read a new identity column value.

Disable, Enable, List and Drop a Trigger (sys.triggers)

SET NOCOUNT ON;
DISABLE TRIGGER dbo.trg_Orders_Review ON dbo.Orders;
SELECT CAST(name AS nvarchar(18)) AS TriggerName, is_disabled
FROM sys.triggers
WHERE parent_id = OBJECT_ID(N'dbo.Orders');

ENABLE TRIGGER dbo.trg_Orders_Review ON dbo.Orders;
DROP TRIGGER IF EXISTS dbo.trg_Orders_Review;

Output:

TriggerName        is_disabled
------------------ -----------
trg_Orders_Audit             0
trg_Orders_Review            1

Once you disable it, it won’t fire. DROP TRIGGER IF EXISTS needs 2016+ (drop trigger if exists, disable a trigger for a bulk load).

Nested and Recursive AFTER INSERT Triggers

A trigger on OrderAudit would nest (32 levels max). One firing itself needs RECURSIVE_TRIGGERS ON (recursive triggers).

Things That Tripped Me Up With the After Insert Trigger

My first set-based version logged nothing. A SET statement resets the row count, and I’d put SET NOCOUNT ON first:

CREATE OR ALTER TRIGGER dbo.trg_Orders_Audit
ON dbo.Orders
AFTER INSERT
AS
    SET NOCOUNT ON;                 -- resets the row count to 0
    IF ROWCOUNT_BIG() = 0 RETURN;
    INSERT INTO dbo.OrderAudit (OrderID, Action, Amount)
    SELECT OrderID, 'INSERT', Amount FROM inserted;
GO
INSERT INTO dbo.Orders (OrderID, Customer, State, Amount)
VALUES (1004, N'Lee Gu', 'IL', 158.00);
SELECT COUNT(*) AS AuditRows FROM dbo.OrderAudit;

Output:

(1 rows affected)
AuditRows  
-----------
          0

(1 rows affected)

• One order, zero audit rows. Count first, as in Step 3.
• A ROLLBACK inside the trigger ends the caller’s transaction (Msg 3609).
• UPDATE(column) is true on every INSERT.

My Takeaways on SQL Server AFTER INSERT Triggers

1. Treat inserted as a set; the scalar version fails silently.
2. Check ROWCOUNT_BIG() before any SET statement.
3. Use OUTPUT ... INTO on tables with triggers.

I ran this on a quiet dev instance; on a busy table, you’d hold the INSERT’s locks longer.

Where to Take It Next With SQL Server Triggers

To change values before saving, use an INSTEAD OF trigger as a before insert; for a ModifiedAt column, an UPDATE trigger.

Other trigger jobs:

• The MariaDB version of CREATE TRIGGER
• Copy deleted rows to another table
• Read a trigger’s definition
• IF…ELSE logic inside a trigger

Frequently Asked Questions