In this SQL Server tutorial, we will discuss how to create a SQL Server Trigger on View with the help of multiple examples.
In one of our tutorials on SQL Server Trigger, we understood how to create a trigger on a table in SQL Server. However, Triggers in SQL Server are not restricted to just tables, we can also create triggers on views as well.
So, here is the list of topics that we will cover
- Can we create trigger on view in SQL Server?
- SQL Server trigger on view syntax
- SQL Server trigger on view example
- SQL Server after trigger on view
- SQL Server trigger alter view
- SQL Server instead of update trigger on view
Can we create trigger on view in SQL Server
A trigger in SQL Server is a special kind of stored procedure that executes automatically based on various database events. Now, out of 3 different kinds of triggers, DML triggers are quite common to use.
DML triggers in SQL Server are triggers that are activated when we try to edit data using DML (Data Manipulation Language) events. Now, the DML events in SQL Server includes the use of INSERT, DELETE, and even UPDATE operation on either a table or a view.
However, please note, while defining DML Trigger in SQL Server, we can either create a FOR/AFTER Trigger or INSTEAD OF TRIGGER. But, we cannot define an AFTER Trigger on view.
So, we can either create a FOR Trigger or an INSTEAD OF Trigger in SQL Server on a view.
Read: Instead of Trigger In SQL Server
SQL Server Trigger on View Syntax
In this section, we will discuss how we can create a SQL Server Trigger on view with the help of an example. As we discussed, in SQL Server, we cannot define an AFTER Trigger on view. So, here we will cover how to create a FOR Trigger on view in SQL Server.
But before we jump to an example, we need to understand the syntax of defining a trigger on view in SQL Server.
CREATE TRIGGER [schema_name.]trigg_name
ON { v_name }
{ FOR | INSTEAD OF } {[INSERT],[UPDATE],[DELETE]}
AS
{sql_statements}
In the above syntax:
- schema_name is an optional keyword used to define the name of the schema where the trigger will be created.
- trigg_name is utilized to define the name of the new trigger in the database.
- After this, the ON { view_name } keyword is utilized to specify the view name on which you want to define the trigger.
- However, we can also use the INSTEAD OF clause. This clause is utilized to run other trigger-defined actions in place of an INSERT, UPDATE, or DELETE statement to a table. Therefore, nothing happens when you insert, update, or delete a statement.
- In the last, after the AS keyword, we can define the set of T-SQL statements that we want to execute.
Read: SQL Server Drop Trigger If Exists
SQL Server Trigger on View Example
Now that we have seen the syntax of how to create a SQL Server Trigger on view, let us take a step further and look at an example of an SQL Server Trigger on View.
For the example illustration, we are going to use the Countries and States table from the GeoNames database,
The Countries table holds records of various Countries including the United States and Canada. On the other hand, the States table consists of state names from various countries given in the Countries table.
id | name | alpha-2-code |
---|---|---|
1 | Austria | AT |
2 | United States | US |
3 | United Kingdom | GB |
4 | Canada | CA |
5 | New Zealand | NZ |
id | name | country_id |
---|---|---|
1 | Texas | 2 |
2 | Florida | 2 |
3 | Alaska | 2 |
4 | California | 2 |
5 | Arizona | 2 |
6 | Hawaii | 2 |
7 | Victoria | 1 |
8 | Manitoba | 4 |
Next, we will create a view using the State table which will return only the state name which belongs to the United States. Here is the code for the view in SQL Server.
USE [GeoNames]
GO
CREATE OR ALTER VIEW [dbo].[getUSAStates]
AS
SELECT States.name AS [State Name],
Countries.name AS [Country Name]
FROM States
INNER JOIN Countries
ON States.country_id = Countries.id
WHERE country_id = 2;
GO
In the above SQL code, we have created a view in the GeoNames database named getUSAStates. This trigger will return all the state names which come under the United States.
Now, if we try to perform the insert operation on the view, it will return an error because the view consists of columns from multiple tables.
In such cases, we can create an INSTEAD OF TRIGGER on view. The role of the view is to perform the insert operation on the States table instead of performing the insert operation on view.
Here is the T-SQL code for the instead of trigger on view in SQL Server.
USE [GeoNames]
GO
CREATE OR ALTER TRIGGER [dbo].[trgUSAStates]
ON [dbo].[getUSAStates]
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
GO
Here we have created instead of trigger in SQL Server named trgUSAStates. This trigger will perform the INSERT operation on the States table instead of performing the INSERT operation on the view.
So, after this, if we perform the INSERT operation on the view, it will be automatically performed on the States table.
USE GeoNames
GO
INSERT INTO getUSAStates
([State Name], [Country Name]) VALUES
('Colorado','United States')
The Above insert operation will be performed on the States table. And if we check the States table, we will get the following result.

Read: Create Trigger in SQL Server for Insert and Update
SQL Server After Trigger on View
In SQL Server, we cannot create an AFTER Trigger on view. And even if we try to define an AFTER Trigger on view in SQL Server, the SQL Server will return an error.
Here is an example where we are creating an AFTER Trigger on the getUSAStates view. But, in the end, the SQL Server instance will return an error.
USE [GeoNames]
GO
CREATE OR ALTER TRIGGER trg_reminder
ON getUSAStates
AFTER INSERT
AS
PRINT 'New Record Inserted in the States table'
GO
As we cannot create an AFTER Trigger on view in SQL Server, the SQL Server will consider the name of the view as invalid for this operation.

Solution: SQL Server After Trigger on View Error
Instead of trying to create an AFTER Trigger on view in SQL Server, we can create an INSTEAD OF Trigger on view. The INSTEAD OF Trigger will execute the given SQL code instead of executing the INSERT, UPDATE, or DELETE operation.
For more details related to INSTEAD OF Trigger, please refer to the Instead of Trigger In SQL Server tutorial.
SQL Server Trigger Alter View
In SQL Server, we generally use the ALTER VIEW statement to modify the definition of a specific view. Now, what will happen when we create a trigger on a view after creating the trigger, we use the ALTER VIEW statement to modify the definition of the view.
So, in SQL Server, when we use the ALTER VIEW statement to modify a view, it will not affect the underlying trigger.
Let us understand this concept using an example in SQL Server. Here is the code of the view that we will use.
USE [GeoNames]
GO
CREATE OR ALTER VIEW [dbo].[vwUSAStateDetails]
AS
SELECT States.name AS [State Name],
Countries.name AS [Country Name]
FROM States
INNER JOIN Countries
ON States.country_id = Countries.id
GO
In the above SQL code, we have created a view named vwUSAStateDetails. This view will fetch state name and country name from the States and Country table respectively.
Here is the SQL code for the trigger that we will use.
USE [GeoNames]
GO
CREATE OR ALTER TRIGGER [dbo].[trgStateDetails]
ON [dbo].[vwUSAStateDetails]
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
GO
Here in this example, we have created an instead of trigger on vwUSAStateDetails which will execute on INSERT operation. This trigger will fetch the country id and instead of performing the insert operation on the view, the trigger will insert the record in the States table.
So, when we perform the insert operation on the vwUSAStateDetails view, it will insert the record automatically in the States table.
USE [GeoNames]
GO
INSERT INTO [vwUSAStateDetails]([State Name], [Country Name])
Values('Alberta', 'Canada')
We are performing an insert operation on the view in the above SQL code. But, the record will be inserted in the States table.

Next, let us move to modifying the view definition using the ALTER VIEW statement.
USE [GeoNames]
GO
ALTER VIEW [dbo].[vwUSAStateDetails]
AS
SELECT States.id AS [State ID],
States.name AS [State Name],
Countries.name AS [Country Name]
FROM States
INNER JOIN Countries
ON States.country_id = Countries.id
GO
Here we have also added the state id to the result of the view. However, this modification in the definition of the view will not have any impact on the trigger which we created above.
SQL Server INSTEAD OF UPDATE Trigger on View
Here is discuss how to create an INSTEAD OF UPDATE Trigger on View in SQL Server.
The INSTEAD OF UPDATE Trigger in SQL Server is a trigger that will not perform the UPDATE operation. However, instead of performing the UPDATE operation, it will perform some other operation that we specify.
Additionally, we can define this trigger either on a table or on a view in SQL Server. Let us discuss how to define INSTEAD OF UPDATE Trigger on a view using an example.
For this, we are going to use the Products and Orders table in SQL Server. And
USE eShop
GO
CREATE OR ALTER VIEW getOrderDetails
AS
SELECT Products.id,
Products.name, Products.price,
Orders.quantity as [order quantity]
FROM Products
INNER JOIN Orders
ON Products.id = Orders.product_id
In the above SQL code, we have created a view named getOrderDetails. This view will return the product and order details from the Products and Orders table respectively.
Next, we will create an INSTEAD OF UPDATE Trigger in this getOrderDetails view. Here is the code for the trigger in SQL Server.
USE eShop
GO
CREATE OR ALTER TRIGGER trgOrderDetailsUpdate
ON getOrderDetails
INSTEAD OF UPDATE
AS
BEGIN
IF(UPDATE(id))
BEGIN
RAISERROR('Cannot update ID, please choose some other column', 16, 1)
RETURN
END
IF(UPDATE(name))
BEGIN
UPDATE Products SET name = inserted.name
FROM inserted
JOIN Products
ON Products.id = inserted.id
PRINT 'Name Updated'
END
if(Update(price))
BEGIN
UPDATE Products SET price = inserted.price
FROM inserted
JOIN Products
on Products.id = inserted.id
PRINT 'Product Price Updated'
END
END
Here in the above code, we have created the INSTEAD OF UPDATE Trigger on the view. This trigger will perform the update operation on the specific columns of the Products table instead of performing the update operation on the view.
Here is the sample T-SQL code, where we are trying to perform the UPDATE operation on the view in SQL Server.
USE eShop
GO
UPDATE getOrderDetails
SET id = 99
WHERE id = 3
UPDATE getOrderDetails
SET name = 'ASUS Vivobook Go 12 L211'
WHERE id = 2
UPDATE getOrderDetails
SET price = '$150'
WHERE id = 3
Here is the result of the above UPDATE statements on the getOrderDetails view in SQL Server.

So, in this section, we have learned how to create a SQL Server INSTEAD OF UPDATE Trigger on view in SQL Server.
Read: Disable Trigger in SQL Server
Conclusion
In this SQL Server tutorial, we have discussed how to create a SQL Server Trigger on View. Here we have covered multiple examples on how to create a SQL Server Trigger on view either for insert or update operation.
Here is the complete list of topics that we have covered.
- Can we create trigger on view in SQL Server?
- SQL Server trigger on view syntax
- SQL Server trigger on view example
- SQL Server after trigger on view
- SQL Server trigger alter view
- SQL Server instead of update trigger on view
You may also like to read the following SQL Server tutorials.
- SQL Server Trigger Before Insert
- SQL Server Create Trigger If Not Exists
- SQL Server Trigger Before Update
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.