SQL Server Trigger Before Insert (and Before Update): Use INSTEAD OF

by

You moved a trigger over from MySQL or Oracle, ran it on SQL Server, and got Msg 195: 'BEFORE' is not a recognized trigger. The code isn’t wrong. The keyword just doesn’t exist here.

Quick answer: there’s no SQL Server trigger before insert. To change values first, write an INSTEAD OF INSERT trigger that does the INSERT itself from inserted. To block bad rows, use AFTER INSERT plus THROW.

The same goes for BEFORE UPDATE: SQL Server gives you INSTEAD OF UPDATE, with the old values in deleted and the new ones in inserted. Here’s the shortest version:

CREATE TRIGGER dbo.trg_Customers_Clean ON dbo.Customers
INSTEAD OF INSERT
AS
    INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit)
    SELECT TRIM(FullName), LOWER(Email), UPPER(State), CreditLimit
    FROM inserted;

What You Need Before You Start With INSTEAD OF Triggers

Tested on: Microsoft SQL Server 2025 (RTM) - 17.0.1000.7, Developer edition, sqlcmd in the VS Code terminal, database df_trgbefore. I reproduced every error below on purpose.

You’ll need ALTER permission on the table. If triggers are new to you, start with how triggers work in SQL Server. Every step reuses this sample: three customers, a credit-limit log and a state lookup.

CREATE TABLE dbo.Customers (
    CustomerID  int IDENTITY(1,1) PRIMARY KEY,
    FullName    nvarchar(16)  NOT NULL,
    Email       varchar(26)   NOT NULL,
    State       char(2)       NOT NULL,
    CreditLimit decimal(10,2) NOT NULL,
    ModifiedAt  datetime2(0)  NULL
);
CREATE TABLE dbo.CreditLog (
    CustomerID int           NOT NULL,
    OldLimit   decimal(10,2) NOT NULL,
    NewLimit   decimal(10,2) NOT NULL
);
CREATE TABLE dbo.Regions (
    State  char(2)      PRIMARY KEY,
    Region nvarchar(8)  NOT NULL
);
INSERT INTO dbo.Regions VALUES ('WA', N'West'), ('CA', N'West'),
    ('TX', N'South'), ('IL', N'Midwest'), ('OH', N'Midwest');
INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit) VALUES
(N'Megan Bowen', 'megan.bowen@example.com', 'WA', 5000.00),
(N'Alex Wilber', 'alex.wilber@example.com', 'TX', 2500.00),
(N'Adele Vance', 'adele.vance@example.com', 'CA', 7500.00);

The examples also use this view over two tables. It shows up again in Step 5:

CREATE VIEW dbo.vCustomerRegion AS
SELECT c.CustomerID, c.FullName, c.State, r.Region, c.CreditLimit,
       c.Email
FROM dbo.Customers AS c
JOIN dbo.Regions   AS r ON r.State = c.State;

Step 1: See Why CREATE TRIGGER … BEFORE INSERT Fails in SQL Server

Here’s the Oracle and MySQL habit, run on SQL Server. I wrapped it in dynamic SQL so TRY…CATCH can print the number:

SET NOCOUNT ON;
BEGIN TRY
    EXEC sys.sp_executesql N'
    CREATE TRIGGER dbo.trg_Customers_BeforeInsert
    ON dbo.Customers
    BEFORE INSERT
    AS
        SET NOCOUNT ON;';
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;

Output:

Msg 195: 'BEFORE' is not a recognized trigger.
SQL Server trigger before insert failing with Msg 195 BEFORE is not a recognized trigger in VS Code
Msg 195: SQL Server only knows FOR, AFTER and INSTEAD OF

Why it happens: T-SQL DML triggers accept exactly FOR, AFTER or INSTEAD OF. There’s no BEFORE and no FOR EACH ROW. Pick the replacement by what your BEFORE trigger did.

Step 2: Change Values Before Insert With an INSTEAD OF INSERT Trigger

This is the real “before insert”. The INSERT never touches the table. SQL Server hands the rows to the trigger in inserted, and the trigger writes the cleaned version. I sent three messy rows in one statement:

CREATE OR ALTER TRIGGER dbo.trg_Customers_Clean
ON dbo.Customers
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit)
    SELECT TRIM(FullName), LOWER(TRIM(Email)), UPPER(State),
           IIF(CreditLimit <= 0, 1000, CreditLimit)
    FROM inserted;
END;
GO
SET NOCOUNT ON;
INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit) VALUES
(N'  Lee Gu ',     ' Lee.Gu@Example.com', 'il', 3000.00),
(N'Nestor Wilke',  'NESTOR.W@example.com', 'oh',    0.00),
(N'Joni Sherman ', 'joni.s@example.com',   'tx', 4200.00);
SELECT CustomerID, FullName, Email, State, CreditLimit
FROM dbo.Customers WHERE CustomerID > 3;

Output:

CustomerID  FullName         Email                      State CreditLimit 
----------- ---------------- -------------------------- ----- ------------
          4 Lee Gu           lee.gu@example.com         IL         3000.00
          5 Nestor Wilke     nestor.w@example.com       OH         1000.00
          6 Joni Sherman     joni.s@example.com         TX         4200.00
SQL Server INSTEAD OF INSERT trigger changing values before insert: trimmed names, lowercase email, uppercase state
Three rows in, three cleaned rows out, one trigger call

Names are trimmed, emails are lowercase, states are uppercase, and Nestor’s $0.00 limit became $1,000.00. The column list skips CustomerID, so the identity value is still generated by the table.

There’s more on the trigger type itself in INSTEAD OF triggers in SQL Server, and on reading the inserted values from inside one.

Is There FOR EACH ROW in SQL Server Triggers?

No. A SQL Server trigger fires once per statement, and inserted holds every row that statement touched: zero, one or 10,000. Step 2 proves it: one INSERT, three rows, all cleaned.

So write the body as one set-based statement: an INSERT with a SELECT, or an UPDATE with a JOIN. Copy a row-level trigger into variables and it handles one row, then quietly drops the rest.

Step 3: Reject Rows Before They’re Saved With AFTER INSERT and THROW

If your BEFORE trigger only validated rows, you don’t need INSTEAD OF. An AFTER trigger runs inside the INSERT’s transaction, so it can still cancel it. It’s the same shape as an AFTER INSERT trigger, with an error instead of an audit row:

CREATE OR ALTER TRIGGER dbo.trg_Customers_Limit
ON dbo.Customers
AFTER INSERT
AS
BEGIN
    IF ROWCOUNT_BIG() = 0 RETURN;
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM inserted WHERE CreditLimit > 50000)
        THROW 50001, N'Credit limit over $50,000 needs approval.', 1;
END;
GO
SET NOCOUNT ON;
BEGIN TRY
    INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit) VALUES
    (N'Lee Gu',      'lee.gu@example.com', 'IL',  3000.00),
    (N'Diego Siciliani', 'diego.s@example.com', 'CA', 75000.00);
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;
SELECT COUNT(*) AS Customers FROM dbo.Customers;

Output:

Msg 50001: Credit limit over $50,000 needs approval.
Customers  
-----------
          3
SQL Server AFTER INSERT trigger using THROW 50001 to reject a two-row insert, customer count stays 3
THROW cancels both rows, not just Diego’s

The count stays at 3. Lee’s valid row was rolled back too, because the whole statement is one unit. If you want the good rows kept, filter them in an INSTEAD OF trigger instead.

THROW vs RAISERROR in a Trigger

THROW ends the batch and rolls back the transaction. RAISERROR only sends a message. Same check, RAISERROR, no ROLLBACK, called without TRY:

CREATE OR ALTER TRIGGER dbo.trg_Customers_Limit
ON dbo.Customers
AFTER INSERT
AS
    IF EXISTS (SELECT 1 FROM inserted WHERE CreditLimit > 50000)
        RAISERROR (N'Credit limit over $50,000 needs approval.', 16, 1);
GO
SET NOCOUNT ON;
INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit)
VALUES (N'Diego Siciliani', 'diego.s@example.com', 'CA', 75000.00);
SELECT COUNT(*) AS Customers FROM dbo.Customers;

Output:

Msg 50000, Level 16, State 1, Server YOUR-SERVER, Procedure trg_Customers_Limit, Line 6
Credit limit over $50,000 needs approval.
Customers  
-----------
          4

Risky: the app gets Msg 50000, yet Diego’s $75,000 row is saved; the count went to 4. If you keep RAISERROR, follow it with ROLLBACK and expect Msg 3609 in the caller. There’s an older RAISERROR pattern in raise an error if a row exists.

Step 4: Replace BEFORE UPDATE With INSTEAD OF UPDATE (Old and New Values)

For the update side, we want three things: log credit-limit changes, clean the email, and stamp ModifiedAt. The old row is in deleted, the new one in inserted, joined on the key:

CREATE OR ALTER TRIGGER dbo.trg_Customers_Update
ON dbo.Customers
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.CreditLog (CustomerID, OldLimit, NewLimit)
    SELECT d.CustomerID, d.CreditLimit, i.CreditLimit
    FROM inserted AS i
    JOIN deleted  AS d ON d.CustomerID = i.CustomerID
    WHERE i.CreditLimit <> d.CreditLimit;

    UPDATE c
    SET FullName    = TRIM(i.FullName),
        Email       = LOWER(TRIM(i.Email)),
        State       = UPPER(i.State),
        CreditLimit = i.CreditLimit,
        ModifiedAt  = SYSDATETIME()
    FROM dbo.Customers AS c
    JOIN inserted      AS i ON i.CustomerID = c.CustomerID;
END;

Now two updates: a 10% raise for Washington and Texas, and a messy new email for Adele:

SET NOCOUNT ON;
UPDATE dbo.Customers SET CreditLimit = CreditLimit * 1.10
WHERE State IN ('WA', 'TX');
UPDATE dbo.Customers SET Email = ' ADELE.V@Example.com'
WHERE CustomerID = 3;

SELECT CustomerID, OldLimit, NewLimit FROM dbo.CreditLog;
SELECT CustomerID, Email, CreditLimit,
       CAST(ModifiedAt AS date) AS Modified
FROM dbo.Customers;

Output:

CustomerID  OldLimit     NewLimit    
----------- ------------ ------------
          1      5000.00      5500.00
          2      2500.00      2750.00
CustomerID  Email                      CreditLimit  Modified        
----------- -------------------------- ------------ ----------------
          1 megan.bowen@example.com         5500.00       2026-10-28
          2 alex.wilber@example.com         2750.00       2026-10-28
          3 adele.v@example.com             7500.00       2026-10-28
SQL Server trigger before update equivalent: INSTEAD OF UPDATE logging old and new credit limit from deleted and inserted
Old vs new values: two limit changes logged, Adele’s email cleaned

Two log rows, 5000.00 to 5500.00 and 2500.00 to 2750.00. Adele’s email change didn’t log anything, since her limit didn’t move. Every row got today’s date in ModifiedAt.

Note: join on a key nobody can change. Identity columns can’t be updated, so CustomerID is safe. To skip work when a column wasn’t touched, see UPDATE() for one column; to compare values in an AFTER trigger, check if a value has changed.

Step 5: Use an INSTEAD OF INSERT Trigger on a View

Views are where INSTEAD OF started. An INSERT into a join view fails with Msg 4405; the trigger tells SQL Server which base table gets the row:

SET NOCOUNT ON;
BEGIN TRY
    EXEC sys.sp_executesql N'INSERT INTO dbo.vCustomerRegion
        (FullName, Email, State, Region, CreditLimit)
        VALUES (N''Lee Gu'', ''lee.gu@example.com'', ''IL'', N''Midwest'', 3000);';
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;
GO
CREATE OR ALTER TRIGGER dbo.trg_vCustomerRegion_Insert
ON dbo.vCustomerRegion
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit)
    SELECT FullName, Email, State, CreditLimit
    FROM inserted;
END;
GO
SET NOCOUNT ON;
INSERT INTO dbo.vCustomerRegion (FullName, Email, State, Region, CreditLimit)
VALUES (N' Lee Gu', 'Lee.Gu@example.com', 'il', N'Midwest', 3000.00);
SELECT CustomerID, FullName, Email, Region FROM dbo.vCustomerRegion
WHERE CustomerID = 4;

Output:

Msg 4405: View or function 'dbo.vCustomerRegion' is not updatable because the modification affects multiple base tables.
CustomerID  FullName         Email                      Region  
----------- ---------------- -------------------------- --------
          4 Lee Gu           lee.gu@example.com         Midwest

Lee came back as lee.gu@example.com in Midwest. The view’s trigger inserted into Customers, and that fired Customers’ own INSTEAD OF trigger, which cleaned the values. That’s a nested trigger, one level deep.

More view cases are in triggers on a view and inserting a row through a view.

When a CHECK Constraint or DEFAULT Beats a Trigger

Plenty of BEFORE INSERT triggers only set a default or reject a value. Constraints do that with no code to maintain:

SET NOCOUNT ON;
ALTER TABLE dbo.Customers ADD CONSTRAINT CK_Customers_CreditLimit
    CHECK (CreditLimit BETWEEN 0 AND 50000);
BEGIN TRY
    INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit)
    VALUES (N'Diego Siciliani', 'diego.s@example.com', 'CA', 75000.00);
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;

Output:

Msg 547: The INSERT statement conflicted with the CHECK constraint "CK_Customers_CreditLimit". The conflict occurred in database "df_trgbefore", table "dbo.Customers", column 'CreditLimit'.

Msg 547, and the trigger from Step 3 isn’t needed for a single-column rule. A column DEFAULT handles “fill in a value when it’s missing”. Keep triggers for rules that read other rows or tables, or that rewrite values.

For a ModifiedAt stamp alone, an AFTER UPDATE trigger is simpler than INSTEAD OF.

Cheat Sheet: BEFORE Triggers in SQL Server

What your BEFORE trigger didSQL Server replacementWatch for
Changed values (NEW.col := …)INSTEAD OF INSERT / UPDATEYou must run the INSERT/UPDATE yourself
Rejected bad rowsAFTER INSERT + THROWWhole statement rolls back
Set a defaultDEFAULT constraintOnly when the column is omitted
Checked one column’s rangeCHECK constraintMsg 547 on failure
Read OLD vs NEWdeleted vs inserted, joined on the keyBoth hold many rows
FOR EACH ROWSet-based INSERT … SELECTNo row-level triggers

Common Mistakes to Avoid With INSTEAD OF Triggers

The trigger forgets to do the insert

This one is quiet. The trigger runs, sqlcmd says rows were affected, and nothing is stored:

CREATE OR ALTER TRIGGER dbo.trg_Customers_Noop
ON dbo.Customers
INSTEAD OF INSERT
AS
    PRINT N'Validated. Looks good.';
GO
INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit) VALUES
(N'Lee Gu',       'lee.gu@example.com', 'IL', 3000.00),
(N'Nestor Wilke', 'nestor.w@example.com', 'OH', 1500.00);
SELECT COUNT(*) AS Customers FROM dbo.Customers;

Output:

Validated. Looks good.

(2 rows affected)
Customers  
-----------
          3

(1 rows affected)

“(2 rows affected)” and still three customers. INSTEAD OF means instead: no INSERT in the body, no rows.

Identity columns and SCOPE_IDENTITY()

SELECT * FROM inserted includes the identity column, so the trigger won’t even compile. Then the caller’s SCOPE_IDENTITY() comes back NULL, because the real INSERT ran in the trigger’s scope:

SET NOCOUNT ON;
BEGIN TRY
    EXEC sys.sp_executesql N'CREATE OR ALTER TRIGGER dbo.trg_Customers_Clean
    ON dbo.Customers INSTEAD OF INSERT AS
        INSERT INTO dbo.Customers SELECT * FROM inserted;';
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;
INSERT INTO dbo.Customers (FullName, Email, State, CreditLimit)
VALUES (N'Lee Gu', 'lee.gu@example.com', 'IL', 3000.00);
SELECT SCOPE_IDENTITY() AS ScopeId, @@IDENTITY AS AtAtId;

Output:

Msg 8101: An explicit value for the identity column in table 'dbo.Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.
ScopeId                                  AtAtId                                  
---------------------------------------- ----------------------------------------
                                    NULL                                        4

Use an explicit column list without the identity column. To get the new ID back, read @@IDENTITY (risky if other triggers insert elsewhere) or look the row up by a natural key. Inside the trigger, inserted.CustomerID is 0 in my run, not the real value.

One INSTEAD OF trigger per action, and no cascading foreign keys

SET NOCOUNT ON;
CREATE TABLE dbo.CustomerOrders (
    OrderID int PRIMARY KEY,
    CustomerID int REFERENCES dbo.Customers ON DELETE CASCADE);
BEGIN TRY
    EXEC sys.sp_executesql N'CREATE TRIGGER dbo.trg_Customers_Second
    ON dbo.Customers INSTEAD OF INSERT AS SET NOCOUNT ON;';
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;
BEGIN TRY
    EXEC sys.sp_executesql N'CREATE TRIGGER dbo.trg_Orders_Delete
    ON dbo.CustomerOrders INSTEAD OF DELETE AS SET NOCOUNT ON;';
END TRY
BEGIN CATCH
    PRINT CONCAT(N'Msg ', ERROR_NUMBER(), N': ', ERROR_MESSAGE());
END CATCH;

Output:

Msg 2111: Cannot create trigger 'trg_Customers_Second' on table 'dbo.Customers' because an INSTEAD OF INSERT trigger already exists on this object.
Msg 2113: Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trg_Orders_Delete' on table 'dbo.CustomerOrders'. This is  because the table has a FOREIGN KEY with cascading DELETE or UPDATE.
SQL Server Msg 2111 INSTEAD OF INSERT trigger already exists and Msg 2113 cascading foreign key error in VS Code
Msg 2111 and Msg 2113: the two INSTEAD OF limits

You can have many AFTER triggers per action, but only one INSTEAD OF trigger per INSERT, UPDATE or DELETE. And INSTEAD OF DELETE or UPDATE isn’t allowed on a table whose foreign key cascades that action.

Expecting the trigger to fire itself again

It won’t: when an INSTEAD OF trigger inserts into its own table, SQL Server skips that trigger and runs constraints and AFTER triggers. That’s why Step 2 doesn’t loop. AFTER triggers that update their own table are a different story (recursive triggers).

If Your SQL Server Trigger Still Fails

  • Msg 195 or Msg 156 near BEFORE: you’re on Oracle/MySQL syntax. Rewrite as Step 2 or Step 3.
  • Rows vanish: the INSTEAD OF body has no INSERT, or its WHERE filters them out.
  • Msg 8101: drop SELECT *; list the columns.
  • Msg 2111: an INSTEAD OF trigger already exists; ALTER it.
  • Only one row changed: the trigger uses variables. Make it set-based.

If you still see odd results, debug the trigger with PRINT and a test transaction.

Next Steps With SQL Server INSTEAD OF Triggers

Before a big load, you may want to disable the trigger and validate the batch yourself. Then turn it back on and run the Step 2 test again.

A few related trigger jobs:

Frequently Asked Questions

Are CHECK constraints checked before or after an INSTEAD OF trigger?

After. Constraints run once the INSTEAD OF trigger finishes and before any AFTER trigger. If one fails, the INSTEAD OF trigger’s work is rolled back and AFTER triggers don’t fire, per the CREATE TRIGGER reference.

Does BULK INSERT fire an INSTEAD OF INSERT trigger?

Not by default. BULK INSERT and bcp skip insert triggers unless you add the FIRE_TRIGGERS option.

Can an AFTER trigger see the values an INSTEAD OF trigger changed?

Yes. The INSERT inside the INSTEAD OF trigger starts the AFTER triggers, and their inserted table holds the rewritten rows (inserted and deleted tables).

Can I put an INSTEAD OF trigger on a view WITH CHECK OPTION?

No. Remove WITH CHECK OPTION with ALTER VIEW first, then create the trigger.