INSTEAD OF Trigger in SQL Server (INSERT, UPDATE, DELETE and Views)

An instead of trigger in SQL Server runs in place of the INSERT, UPDATE or DELETE that fired it. The original statement never touches the table, so the trigger writes the rows itself. This one cleans emails and skips blank names:

CREATE OR ALTER TRIGGER dbo.trg_Customers_Insert
ON dbo.Customers
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.Customers (FullName, Email, City)
    SELECT TRIM(FullName), LOWER(TRIM(Email)), City
    FROM inserted
    WHERE TRIM(FullName) <> N'';
END;
GO
INSERT INTO dbo.Customers (FullName, Email, City) VALUES
    (N' Olivia Hayes ', ' Olivia.Hayes@Mail.com', N'Boise'),
    (N'   ', 'blank@mail.com', N'Reno');
SELECT CustomerID, FullName, Email
FROM dbo.Customers WHERE CustomerID > 3;

Running it gives:

(2 rows affected)
CustomerID  FullName             Email                   
----------- -------------------- ------------------------
          4 Olivia Hayes         olivia.hayes@mail.com   

(1 rows affected)

Check your work: only CustomerID 4 came back, lowercase. The blank row never arrived, yet sqlcmd still said 2 rows affected.

Sample Tables for the INSTEAD OF Examples

Tested on SQL Server 2025 (17.0.1000.7) Developer, sqlcmd in VS Code. Each example starts from this:

CREATE TABLE dbo.Customers (
    CustomerID int IDENTITY PRIMARY KEY,
    FullName   nvarchar(20) NOT NULL,
    Email      varchar(24)  NOT NULL UNIQUE,
    City       nvarchar(10) NULL,
    IsDeleted  bit          NOT NULL DEFAULT 0
);
CREATE TABLE dbo.Orders (
    OrderID    int IDENTITY(1001, 1) PRIMARY KEY,
    CustomerID int NOT NULL
        CONSTRAINT FK_Orders_Customers REFERENCES dbo.Customers (CustomerID),
    Amount     decimal(10,2) NOT NULL,
    Status     varchar(7)    NOT NULL DEFAULT 'Open'
);
INSERT INTO dbo.Customers (FullName, Email, City) VALUES
    (N'Hannah Brooks',  'hannah.brooks@mail.com', N'Denver'),
    (N'Tyler Reed',     'tyler.reed@mail.com',    N'Austin'),
    (N'Grace Miller',   'grace.miller@mail.com',  N'Denver');
INSERT INTO dbo.Orders (CustomerID, Amount, Status) VALUES
    (1, 120.00, 'Shipped'), (2, 89.50, 'Open'), (3, 240.00, 'Open');
GO
CREATE VIEW dbo.vCustomerOrders AS
SELECT o.OrderID, c.FullName, c.Email, o.Amount
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID;

How the INSTEAD OF INSERT Trigger Works

One set-based INSERT from inserted handles 1 row or 500, and because it targets the same table, SQL Server runs it as if the trigger weren’t there. TRIM needs SQL Server 2017+.

SQL Server has no BEFORE trigger. This is the stand-in; the BEFORE INSERT walkthrough goes further.

INSTEAD OF DELETE Trigger for a Soft Delete

Now a DELETE should only flag rows:

CREATE OR ALTER TRIGGER dbo.trg_Customers_SoftDelete
ON dbo.Customers
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE c SET IsDeleted = 1
    FROM dbo.Customers AS c
    JOIN deleted AS d ON d.CustomerID = c.CustomerID;
END;
GO
DELETE FROM dbo.Customers WHERE City = N'Denver';
SELECT CustomerID, FullName, City, IsDeleted
FROM dbo.Customers;

Result:

(2 rows affected)
CustomerID  FullName             City       IsDeleted
----------- -------------------- ---------- ---------
          1 Hannah Brooks        Denver             1
          2 Tyler Reed           Austin             0
          3 Grace Miller         Denver             1

(3 rows affected)

You should see all three rows, Hannah and Grace flagged. Without the trigger, I got Msg 547. Both have orders behind a foreign key.

INSTEAD OF UPDATE Trigger: Compare inserted and deleted

deleted holds the old values, inserted the new ones. Only Open orders may change:

CREATE OR ALTER TRIGGER dbo.trg_Orders_Update
ON dbo.Orders
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE o SET Amount = i.Amount, Status = i.Status
    FROM dbo.Orders AS o
    JOIN inserted AS i ON i.OrderID = o.OrderID
    JOIN deleted  AS d ON d.OrderID = i.OrderID
    WHERE d.Status = 'Open';
    PRINT CONCAT(@@ROWCOUNT, ' changed');
END;
GO
UPDATE dbo.Orders SET Amount = Amount + 10;
SET NOCOUNT ON;
SELECT OrderID, Amount, Status FROM dbo.Orders;

Result:

2 changed

(3 rows affected)
OrderID     Amount       Status 
----------- ------------ -------
       1001       120.00 Shipped
       1002        99.50 Open   
       1003       250.00 Open

I got 2 changes, but sqlcmd said 3 rows affected (the rows targeted). To react to one column, the UPDATE() check in an AFTER trigger is simpler.

How Do You Insert Into a View With Multiple Tables?

vCustomerOrders joins two tables, so a plain INSERT fails:

SET NOCOUNT ON;
BEGIN TRY
    EXEC (N'INSERT INTO dbo.vCustomerOrders (FullName, Email, Amount)
            VALUES (N''Noah Carter'', ''noah.carter@mail.com'', 64.00);');
END TRY
BEGIN CATCH
    PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE());
END CATCH;

The error:

Msg 4405: View or function 'dbo.vCustomerOrders' is not updatable because the modification affects multiple base tables.

The fix adds a new customer, then the order:

CREATE OR ALTER TRIGGER dbo.trg_vCustomerOrders_Insert
ON dbo.vCustomerOrders
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.Customers (FullName, Email)
    SELECT DISTINCT i.FullName, i.Email FROM inserted AS i
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Customers AS c
                      WHERE c.Email = i.Email);
    INSERT INTO dbo.Orders (CustomerID, Amount)
    SELECT c.CustomerID, i.Amount FROM inserted AS i
    JOIN dbo.Customers AS c ON c.Email = i.Email;
END;
GO
SET NOCOUNT ON;
INSERT INTO dbo.vCustomerOrders (FullName, Email, Amount) VALUES
    (N'Noah Carter', 'noah.carter@mail.com', 64.00),
    (N'Tyler Reed',  'tyler.reed@mail.com',  42.25);
SELECT OrderID, FullName, Amount
FROM dbo.vCustomerOrders WHERE OrderID > 1003;

Result:

OrderID     FullName             Amount      
----------- -------------------- ------------
       1004 Noah Carter                 64.00
       1005 Tyler Reed                  42.25

Noah Carter became a customer with order 1004; Tyler Reed just got order 1005. More patterns: SQL Server triggers on views.

AFTER vs INSTEAD OF Trigger in SQL Server

	AFTER (FOR)	INSTEAD OF
Fires	After the statement succeeds	In place of the statement
Constraints checked	Before the trigger	After the trigger
On views	No	Yes
Per action per table	Many	One

Just logging rows? You’ll want an AFTER INSERT trigger.

INSTEAD OF Trigger Errors: Msg 2111 and 2113

SET NOCOUNT ON;
CREATE TABLE dbo.OrderNotes (
    OrderID int REFERENCES dbo.Orders (OrderID) ON DELETE CASCADE);
EXEC (N'CREATE TRIGGER dbo.trg_Del1 ON dbo.Customers
        INSTEAD OF DELETE AS SET NOCOUNT ON;');
BEGIN TRY
    EXEC (N'CREATE TRIGGER dbo.trg_Del2 ON dbo.Customers
            INSTEAD OF DELETE AS SET NOCOUNT ON;');
END TRY BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
BEGIN TRY
    EXEC (N'CREATE TRIGGER dbo.trg_Notes ON dbo.OrderNotes
            INSTEAD OF DELETE AS SET NOCOUNT ON;');
END TRY BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

The errors:

Msg 2111: Cannot create trigger 'trg_Del2' on table 'dbo.Customers' because an INSTEAD OF DELETE trigger already exists on this object.
Msg 2113: Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trg_Notes' on table 'dbo.OrderNotes'. This is  because the table has a FOREIGN KEY with cascading DELETE or UPDATE.

Msg 2113 hit OrderNotes, the child holding the cascading key; the parent Orders didn’t raise it. A view WITH CHECK OPTION gave Msg 2112. For Msg 2111, use CREATE OR ALTER or drop the old trigger first.

Where to Go Next With Triggers

Next step: read values from the inserted table. On MariaDB, see MariaDB CREATE TRIGGER.

• For a hard delete that logs rows, see a FOR DELETE trigger
• Disable a trigger temporarily

Frequently Asked Questions