SQL Server Trigger On View

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.

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