In this SQL Server tutorial, we will learn and comprehend how INSTEAD OF TRIGGER in SQL Server works. Also, we will discuss how we can define an instead of trigger in SQL Server either for table or view.
Generally, when we define a trigger in SQL Server, a trigger performs some operation based on certain database actions. However, with the help of INSTEAD OF TRIGGER, we can perform some actions instead of the specified ones.
Now, to understand the idea of INSTEAD OF TRIGGER in SQL Server better, we will discuss and learn various examples. The complete list of examples that we will address is given below.
- Instead of Trigger in SQL Server
- Syntax of Instead of Trigger in SQL Server
- Instead of Trigger in SQL Server With Example
- Instead of Update Trigger in SQL Server
- Instead of Delete Trigger in SQL Server
- After vs Instead of Trigger in SQL Server
Instead of Trigger in SQL Server
In this SQL Server section, we will learn and understand how to use INSTEAD OF TRIGGER in SQL Server.
The INSTEAD OF TRIGGER in SQL Server is a trigger that allows us to skip DELETE, INSERT and UPDATE statements in a table and it executes other statements defined in the trigger instead. The INSERT, DELETE and UPDATE statements don’t occur at all.
In other words, the INSTEAD OF trigger is used to skip DML statements and execute the other statements.
Syntax of Instead of Trigger in SQL Server
Once we got an idea of instead of trigger in SQL Server, let us discuss how we can define a trigger in SQL Server. So, here is the syntax of defining an instead of trigger in SQL Server.
CREATE TRIGGER [SCHEMA_NAME].[YOUR_TRIGGER_NAME]
ON [ YOUR_TABLE_NAME | VIEW_NAME]
INSTEAD OF { [INSERT], [DELETE], [UPDATE] }
AS
{ YOUR_SQL_STATEMENTS }
In the above syntax:
- First, we need to specify the name of the trigger while specifying the CREATE TRIGGER statement.
- Second, specify the table name with which the trigger will be associated.
- Third, specify the event such as DELETE, INSERT or UPDATE statement to which trigger is released in the INSTEAD OF clause. Moreover, we can specify more than one event.
- Fourth, we need to specify the body of the trigger after the AS keyword. Moreover, the trigger body may contain more than one TRANSACT-SQL statement.
With the end of this section, we have understood what is instead of trigger in SQL Server. Also, we have discussed the syntax to define an INSTEAD OF TRIGGER.
Instead of Trigger in SQL Server With Example
Next, let us discuss how we can use this syntax in SQL Server. For this, we will illustrate an example using a table and a view in SQL Server.
For example, we have States and Countries table in SQL Server. Here is the ER diagram for both tables.

Now, based on these tables, we have created a view named vwStateDetails. Here is the code for this view in SQL Server.
USE GeoNames
GO
CREATE VIEW vwStateDetails
AS
SELECT States.id, States.name AS [State Name], Countries.name AS [Country Name]
FROM States
INNER JOIN Countries
ON States.country_id = Countries.id
The above view in SQL Server will return both the state name and its particular country as a result.
Now, the SQL Server will return an error if we try to perform an insert operation on this vwStateDetails view.
USE GeoNames
GO
INSERT INTO vwStateDetails([State Name], [Country Name])
VALUES ('New Jersey', 'United States')
Here is the error message in SQL Server.

Now, to overcome this error in SQL Server, we can define an INSTEAD OF TRIGGER. The role of this INSTEAD OF TRIGGER is to fetch the inserted values. And instead of inserting values in the view, it will insert values in the States and Countries tables respectively.
Here is the code for the INSTEAD OF TRIGGER in SQL Server.
USE GeoNames
GO
CREATE TRIGGER trgvwStateDetails
ON vwStateDetails
INSTEAD OF INSERT
AS
BEGIN
--Fetch country_id for States table
DECLARE @CountryId int
SELECT @CountryId = Countries.id
FROM Countries
INNER JOIN INSERTED
on inserted.[Country Name] = Countries.name
--If the CountryId is null then throw an error
IF(@CountryId is null)
BEGIN
RAISERROR('Invalid Country Name', 16, 1)
RETURN
END
--Insert data in States table
INSERT INTO States(name, country_id)
SELECT [State Name], @CountryId
FROM INSERTED
End
In the above example, we have defined a trigger that inserts a record in the States table instead of inserting data in the vwStateDetails view.
Now, if we perform the insert operation again on the vwStateDetails view, it will not insert data in the view. Instead, the trigger will be executed and it will insert a new record in the States table.

At the end of this section, we get a clear idea of how to the INSTEAD OF TRIGGER Syntax in SQL Server. And how to create an INSTEAD OF INSERT TRIGGER in SQL Server.
Read: SQL Server Date Format
Instead of Update Trigger in SQL Server
In the previous section, we have seen how to define an INSTEAD OF TRIGGER for INSERT statement in SQL Server. Now, in this section, we will take a step further and understand how to define an INSTEAD OF TRIGGER for UPDATE operation in SQL Server.
For the example implementation, let us assume there is a wrong entry in the States table given in the previous section.

Now, to update its data, we are using the same vwStateDetails view in SQL Server. However, just like the INSERT statement, we cannot directly perform the UPDATE operation on the vwStateDetails view. Even if we try to perform it, the SQL Server will return an error.
So, we will create an INSTEAD OF UPDATE TRIGGER in SQL Server on the vwStateDetails view. And the SQL code for this task is given below.
USE GeoNames
GO
CREATE OR ALTER TRIGGER trgUpdateStateDetails
ON vwStateDetails
INSTEAD OF UPDATE
AS
BEGIN
UPDATE dbo.States
SET name = ins.[State Name]
FROM dbo.States
INNER JOIN INSERTED ins on States.id = ins.id;
END
In the above SQL code, we have defined an INSTEAD OF TRIGGER for UPDATE named trgUpdateStateDetails. This trigger will update the States table as specified for the vwStateDetails.
Next, let us update the vwStateDetals and correct the state name from Kalifornia to California.
USE GeoNames
GO
UPDATE vwStateDetails
SET [State Name] = 'California'
WHERE id = 1006
After executing the above SQL, the trigger will initialize and it will update the state name to California where the id is 6. Here is the result of the States table in SQL Server.

So, in this SQL Server section, we have learned how to create an INSTEAD OF UPDATE TRIGGER IN SQL Server using an example.
Read: SQL Server First Day Of Month
Instead of Delete Trigger in SQL Server
Just like creating INSTEAD OF TRIGGER for INSERT and DELETE, we can also create an INSTEAD OF TRIGGER in SQL Server for DELETE operation. Let us understand this whole concept using an example.
For the example implementation, we will try to perform the DELETE operation on the vwStateDetails view. However, when we try to perform the delete operation, the SQL Server instance will return an error.

So, to overcome this issue in SQL Server, we are going to create INSTEAD OF DELETE TRIGGER on the vwStatesDetails view. Here is the T-SQL code for the trigger in SQL Server.
USE GeoNames
GO
CREATE OR ALTER TRIGGER trgStateDetailsDelete
ON vwStateDetails
INSTEAD OF DELETE
AS
BEGIN
DECLARE @state_id int
SELECT @state_id = States.id
FROM States
INNER JOIN DELETED
ON States.id = DELETED.id
IF(@state_id is NULL )
BEGIN
RAISERROR('Invalid ID', 16, 1)
RETURN
END
ELSE
BEGIN
DELETE FROM States
WHERE id = @state_id
END
END
In the above example, we created a trigger that will fetch the state id value from the DELETED table. And instead of trying to delete the record from view, it will perform the DELETE operation on the States table.
USE GeoNames
GO
DELETE FROM vwStateDetails
WHERE id = 5
GO
Now, when we try to perform the DELETE operation on the view, the trigger will be automatically executed. And it will delete the record from the States table.
Note: Please note that here in this example we have deleted a record from the States table where the state id is 5 and state name is Georgia.
Here is the resultset of the States table after performing the delete operation on the view in SQL Server.

At the end of this section, we understood how to create INSTEAD OF DELETE TRIGGER in SQL Server using an example.
Read: SQL Server view order by
After vs Instead of Trigger in SQL Server
In this SQL Server section, we will understand the key difference between AFTER TRIGGER and INSTEAD OF TRIGGER in SQL Server.
Generally, the FOR / AFTER trigger in SQL Server is utilized to perform certain operations once the INSERT, DELETE, or UPDATE operation is successfully executed. However, INSTEAD OF Trigger in SQL Server is the trigger that will execute some other operation instead of the specified operation.
Let us understand the difference between AFTER Trigger and INSTEAD OF Trigger in SQL Server using an example. So, for the example demonstration, we will create one AFTER Trigger and one INSTEAD OF Trigger on the States table for the INSERT operation.
And we will check how AFTER Trigger and INSTEAD OF Trigger is different from one another.
AFTER Trigger in SQL Server Example
USE GeoNames
GO
CREATE TRIGGER dbo.trgStatesInsert
ON dbo.States
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.StatesLog(state_id, operation, updatedDate)
SELECT id ,'INSERT',GETDATE() FROM INSERTED;
END
In the above example, we have created a trigger in SQL Server named trgStatesInsert. This trigger will insert a record in the StatesLog table after the INSERT statement is executed successfully.
USE GeoNames
GO
INSERT INTO dbo.States(name, country_id)
VALUES ('Arizona', 2)
So, whenever we perform an insert operation on the States table, the trigger will be executed after the INSERT statement. And the trigger will make an entry in the StatesLog table.
Here is the data of the States and StatesLog table after the insert operation.

INSTEAD OF Trigger in SQL Server Example
USE GeoNames
GO
CREATE TRIGGER dbo.trgStatesInsteadOfInsert
ON dbo.States
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.StatesLog(state_id, operation, updatedDate)
SELECT id ,'INSERT',GETDATE() FROM INSERTED;
END
In the above example, we have created a trigger in SQL Server named trgStatesInsteadOfInsert. This trigger will insert a record in the StatesLog table instead of performing the insert operation on the States table.
USE GeoNames
GO
INSERT INTO dbo.States(name, country_id)
VALUES ('Ohio', 2)
Now, when we perform the INSERT operation on the States table, the trigger will make an entry in the StatesLog table instead of inserting data in the States table.
Here is the data of the States and StatesLog table after executing the INSERT statement.

At the end of this section, we have covered the critical difference between AFTER trigger and INSTEAD OF Trigger in SQL Server.
Conclusion
So, in this SQL Server tutorial, we learned how Instead of Trigger In SQL Server works. Moreover, to help you understand the concept better, we also discussed and covered various examples. The complete list of topics we have addressed is given below.
- Instead of Trigger in SQL Server
- Syntax of Instead of Trigger in SQL Server
- Instead of Trigger in SQL Server With Example
- Instead of Update Trigger in SQL Server
- Instead of Delete Trigger in SQL Server
- After vs Instead of Trigger in SQL Server
Also, take a look at some more SQL Server tutorials.
- Alter view in SQL Server
- SQL Server OUTER JOIN
- Trigger For Delete SQL Server
- Indexed views in SQL Server
- SQL Server String_agg
- View SQL Server Error Logs
- SQL Server Drop Trigger If Exists
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.