SQL Server Trigger On View

This  SQL Server tutorial will discuss the SQL Server Trigger on view. We will discuss and draw lessons from several cases to help you comprehend the subject better. The whole list of subjects we’ll discuss is provided below.

  • SQL Server Trigger On View
  • SQL Server After Trigger on View
  • SQL Server Trigger View Instead of
  • SQL Server Trigger Alter View
  • SQL Server Instead of Update Trigger on View
  • Can We create Trigger on View SQL Server
  • SQL Server View Triggers on Table

SQL Server Trigger On View

In this SQL Server section, we will learn and understand how to use the SQL Server Trigger on View Table by the query, which will be explained with the help of an illustrated example.

A VIEW in the SQL Server is like a virtual table that possesses data from numerous tables. And it doesn’t hold any data and it does not physically exist in the database. Similar to a SQL table, the view name should be unique in a database.

It contains a set of predefined SQL queries which will fetch data from the database. It can contain database tables from single or multiple databases as well.

It doesn’t require any storage as it doesn’t exist physically in the database. In a VIEW, we can also control user security by accessing the data from the database tables. We can allow users to get data from the VIEW and user doesn’t need to take permission for each table or column to fetch data.

Here we see a sample example of the SQL Server trigger on VIEW by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE VIEW STATE_DETAIL
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=20;

CREATE TRIGGER INSTEADOF_UPDATE
ON STATE_DETAIL
INSTEAD OF UPDATE
AS 
BEGIN
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=20
ORDER BY STATE_ID ASC
END;
GO

UPDATE STATE_DETAIL
SET FULL_NAME='John Cena'
WHERE STATE_ID=25;

As we see in the above query, we have created a view called STATE_DETAIL for the CANADA_STATES table by using the CREATE VIEW statement. In the AS clause, the SELECT statement is used to retrieve all records from the CANADA_STATES table with the WHERE condition.

In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the SELECT statement retrieves all records from the CANADA_STATES table otherwise vice-versa. It is the simplest form of the VIEW.

In the second query, we have created a trigger called INSTEADOF_UPDATE on the STATE_DETAIL view by using the CREATE TRIGGER statement.

Then we have used the INSTEAD OF trigger on the UPDATE statement which means that once the INSTEAD OF trigger is fired then it will skip the UPDATE statement effect on the CANADA_STATES table. But instead, it will return the SQL STATEMENT which is used inside the CREATE TRIGGER statement.

In the BEGIN statement, we have used the SELECT statement to retrieve all records from the CANADA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the CANADA_STATES table.

And we have also arranged the records in ascending order by using the ORDER BY expression ASC in the CANADA_STATES table. If the WHERE condition gets a TRUE value then the SELECT statement retrieves all records from the CANADA_STATES table.

But if the SELECT statement gets executed but it retrieves an empty record set from the CANADA_STATES table only when the WHERE condition gets a FALSE value.

As we see that the INSTEAD OF trigger is fired and the UPDATE statement is triggered but it will skip execution of the UPDATE statement on the CANADA_STATES table. Here the execution of the UPDATE statement says:

  • In the UPDATE statement, it will update and set a string_value of the FULL_NAME column as John Cena with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 25 from the CANADA_STATES table.
    • If the WHERE condition turns out to be TRUE then the UPDATE statement will update and set a new string_value of the FULL_NAME column in the CANADA_STATES table. But it will not happen because of the INSTEAD OF trigger fired.
    • If the UPDATE statement gets executed and no value gets updated in the CANADA_STATES table only when the WHERE condition gets a FALSE value.
Sql server trigger on view example
Example of SQL Server Trigger On View

We hope that you have understood how to use the trigger on the VIEW for the table by the query. For a better understanding, we have used an example and explained it in depth.

Also, check: Execute Trigger in SQL Server

SQL Server After Trigger on View

Here we will learn and understand how to use the SQL Server AFTER trigger on VIEW of the table by the query, which will be explained with the help of an illustrated example.

As per SQL Server guidelines, we can reference a view by using the INSTEAD OF trigger and we can’t define the DML triggers on the local or global temporary tables. Here is an example of using the AFTER/ FOR trigger for view by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE VIEW vw_canadastates
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=20;

CREATE TRIGGER AFTER_DML
ON vw_canadastates
FOR UPDATE
AS 
BEGIN
SELECT * FROM CANADA_STATES
WHERE STATE_ID>60
ORDER BY STATE_ID DESC
END;

UPDATE vw_canadastates
SET FULL_NAME='Johnny Walker'
WHERE STATE_ID=61;

As per the above query, an error has been raised on the CREATE TRIGGER statement by using the FOR trigger. As the error says “Msg 8197, Level 16, State 6, Procedure AFTER_DML, Line 1 [Batch Start Line 7]
The object ‘vw_canadastates’ does not exist or is invalid for this operation.

Sql server after trigger on view example
Example of SQL Server After Trigger on VIew

INSTEAD OF trigger on View:

USE SQLSERVERGUIDES;

CREATE VIEW vw_canadastates
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=20;

CREATE TRIGGER AFTER_DML
ON vw_canadastates
INSTEAD OF UPDATE,INSERT, DELETE
AS 
BEGIN
SELECT * FROM CANADA_STATES
WHERE STATE_ID>59
ORDER BY STATE_ID DESC
END;

UPDATE vw_canadastates
SET FULL_NAME='Johnny Walker'
WHERE STATE_ID=60;

INSERT INTO vw_canadastates
VALUES(68,'Adam Gilcristh','Manitoba',5120);

DELETE FROM vw_canadastates
WHERE STATE_ID=57;

In this aforementioned query, a view is created called VW_CANADASTATES on the SQL STATEMENTS by using the CREATE VIEW statement. In the AS clause, the SELECT statement retrieves all records from the CANADA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the CANADA_STATES table.

In the second query, we have created a trigger called AFTER_DML statement on the VW_CANADASTATES table by using the CREATE TRIGGER statement. Then we have used the INSTEAD OF trigger on the DML STATEMENT i.e; (INSERT, DELETE, UPDATE) which means that once the INSTEAD OF trigger is fired then the DML STATEMENT will be triggered but it will not make any effect on the VW_CANADASTATES view. But it will run the SQL STATEMENT which is used inside the BEGIN and END statements.

In the BEGIN statement, the SELECT statement is used to retrieve all records from the CANADA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN operator to find a value greater than 59 from the CANADA_STATES table.

And then we have arranged the STATE_ID column in descending order by using the ORDER BY expression DESC. To end it, we have used the END statement.

If the WHERE condition turns out to be TURE then the SELECT statement will retrieve all records from the CANADA_STATES table. But if the SELECT statement retrieves an empty record set from the CANADA_STATES table and it has been executed then it means that the WHERE condition gets a FALSE value.

As we know that the INSTEAD OF trigger has skipped the execution of the DML STATEMENT and can’t make any effect on the VIEW. Here the execution of the DML STATEMENT says:

  • In the UPDATE statement, it will update and set a new string_value of the FULL_NAME column as Johnny Walker on the VW_CANADASTATES view with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 60 from the VW_CANADASTATES view.
    • If the UPDATE statement can’t update with a new value for that column in the VW_CANADASTATES view only when the WHERE condition turns out to be Honourable.
    • If the WHERE condition turns out to be TRUE then the UPDATE statement will update the new value for that column in the VW_CANADASTATES view.
  • In the INSERT statement, it will insert a new record into the VW_CANADASTATES view.
  • In the DELETE statement, it will delete one record from the VW_CANADASTATES view with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 57 from the VW_CANADASTATES view.
    • If the WHERE condition turns out to be TRUE then the DELETE statement will delete the record from the VW_CANADASTATES view otherwise vice-versa.
Example of Sql server after trigger on view
Example of SQL Server INSTEAD OF trigger on VIEW

We hope that you have understood the subtopic “SQL Server After Trigger on View” by not using the AFTER trigger but we have used the INSTEAD OF trigger as per SQL Server guidelines. For a better description, we have used an example and defined it in depth.

Read: SQL Server Trigger Before Update

SQL Server Trigger View Instead of

We will learn and understand how to use the SQL Server INSTEAD OF trigger on the VIEW of the table by the query, which is explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE VIEW VW_HARVARD
AS 
SELECT * FROM HARVARD_UNIVERSITY;

CREATE TRIGGER INSTEADOF_HARVARD
ON VW_HARVARD
INSTEAD OF INSERT
AS
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=18
ORDER BY STUDENT_ID DESC;

INSERT INTO VW_HARVARD
VALUES(23,'Emma','Watson','Watson.emma@hollywood.com','Female','2022-05-07','2028-07-09');

In the first query, we have created a view called VW_HARVARD for the SQL STATEMENT by using the CREATE VIEW statement.

In the second query, a trigger is created called INSTEADOF_HARVARD on the VW_HARVARD view by using the CREATE TRIGGER statement. Then we have used the INSTEAD OF trigger with the INSERT statement which means that once the INSTEAD OF trigger is fired then the INSERT statement will be executed but it will not make any effect on the VW_HARVARD view.

Instead, it will run the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be executed, and will show the records in the result set.

In the AS clause, the SELECT statement is used to retrieve all records from the HARVARD_UNIVERSITY table with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 18 from the VW_HARVARD view.

And we have arranged the records of the STUDENT_ID column in descending order with the help of the ORDER BY expression DESC keyword.

As we know, if the INSTEAD OF trigger is fired then it will skip the execution of the DML STATEMENT on the VW_HARVARD view. Here is the execution of the INSERT statement says:

  • In the INSERT statement, it will insert a new record into the HARVARD_UNIVERISTY table but it will skip the effect on it.
Sql server trigger view instead of example
Example of SQL Server INSTEAD OF trigger on VIEW

We hope that you have understood the subtopic “SQL Server Trigger View Instead of” by using the SQL Server INSTEAD OF trigger for the table by view in the query. We have used a sample example and defined it in deepness, for better understanding.

Read: SQL Server Trigger Before Insert

SQL Server Trigger Alter View

In the SQL Server section, we will learn and understand how to use the ALTER VIEW statement with the TRIGGER for the table by the query, which will be explained with the help of an illustrated example.

In SQL Server, you need to have ALTER permission on the view to modify it. In addition, depending on the activities you are carrying out, you will need to have specific permissions on the underlying objects. For instance, you need to have SELECT permission on the table if you refer to a SELECT statement on it in your view. Similar to this, you must be aware of the permissions on any other objects your view references, such as views or functions.

The main use of the SQL Server ALTER VIEW statement is to modify the view_name which is used for the SQL STATEMENT. But if we want to modify the old SQL STATEMENT then we can use the ALTER VIEW statement.

Let’s see a sample example of the SQL Server INSTEAD OF trigger on the ALTER VIEW statement of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

ALTER VIEW VW_INSTEADOF_HARVARD
AS 
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=10;

CREATE TRIGGER INSTEADOF_HARVARD
ON VW_INSTEADOF_HARVARD
INSTEAD OF DELETE
AS
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=15
ORDER BY STUDENT_ID ASC;

DELETE FROM VW_INSTEADOF_HARVARD
WHERE STUDENT_ID=20;

In the first query, we have used the ALTER VIEW statement to modify the SQL STATEMENT under the view_name as VW_INSTEADOF_HARVARD. In the AS clause says, the SELECT statement is used to retrieve all records from the HARVARD_UNIVERSITY table with the WHERE condition.

In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 10 from the HARVARD_UNIVERSITY table.

If the SELECT statement retrieves all records from the HARVARD_UNIVERSITY table only when the WHERE condition turns out to be TRUE otherwise vice-versa.

In the second query, a trigger is created called INSTEADOF_HARVARD on the VW_INSTEADOF_HARVARD view by using the CREATE TRIGGER statement. Then we have used the INSTEAD OF trigger on the DELETE statement which means that once the INSTEAD OF trigger is fired, it will skip the execution effect on the VW_INSTEADOF_HARVARD view.

But it will instead run the SQL STATEMENT which is used inside the CREATE TRIGGER statement.

In the AS clause, the SELECT statement retrieves all records from the HARVARD_UNIVERSITY table with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 15 from the HARVARD_UNIVERSITY table.

And we have also arranged the STUDENT_ID column in ascending order with the ORDER BY expression ASC clause.

As we know that INSTEAD OF the trigger being fired and the DELETE statement will trigger. But it will make any effect on the VW_INSTEADOF_HARVARD view because it will skip the effect on the HARVARD_UNIVERSITY table.

In the DELETE statement, it will delete one record from the HARVARD_UNIVERSITY table with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find a value equal to 20 from the HARVARD_UNIVERSITY table.

If the DELETE statement gets executed and deletes no record from the HARVARD_UNIVERSITY table only when the WHERE condition gets a FALSE value. But if the WHERE condition turns out to be a TRUE value then the DELETE statement will delete one record from the HARVARD_UNIVERSITY table.

SQL Server View Triggers on Table example
Example of SQL Server Trigger Alter View

We hope that you have understood the subtopic “SQL Server Trigger Alter View” by using the SQL Server ALTER VIEW statement with the trigger for the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: Disable Trigger in SQL Server

SQL Server Instead of Update Trigger on View

In this SQL Server subtopic tutorial, we will learn and understand how to use the SQL Server INSTEAD OF trigger with the UPDATE statement on VIEW of the table by the query, which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE VIEW VW_INSTEADOF_HARVARD
AS 
SELECT * FROM HARVARD_UNIVERSITY;

CREATE TRIGGER INSTEADOF_TRIGGER
ON VW_INSTEADOF_HARVARD
INSTEAD OF UPDATE
AS
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=20 
ORDER BY STUDENT_ID DESC;

UPDATE VW_INSTEADOF_HARVARD
SET STUDENT_FIRSTNAME='Daniel'
WHERE STUDENT_ID=20;

In the aforementioned query, a trigger is created called VW_INSTEADOF_HARVARD on the SQL STATEMENT by using the CREATE VIEW table.

As we see in the above query, we have created a trigger called INSTEADOF_TRIGGER on the VW_INSTEADOF_HARVARD view by using the CREATE TRIGGER statement.

Then we have used the INSTEAD OF trigger on the UPDATE statement which means that once the INSTEAD OF trigger is fired then the DML STATEMENT will be triggered and it will make no effect on the VW_INSTEADOF_HARVARD view. And the SQL STATEMENT will be executed and show the result set for the output.

In the AS clause, the SELECT statement retrieves all records from the HARVARD_UNIVERSITY table with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the HARVARD_UNIVERSITY table.

And we have also arranged the STUDENT_ID column in descending order with the ORDER BY expression DESC clause.

If the WHERE condition gets a TRUE value then the SELECT statement will retrieve all records from the HARVARD_UNIVERSITY table. But if the SELECT statement gets executed and retrieves an empty record set from the HARVARD_UNIVERSITY table only when the WHERE condition turns out to be a FALSE value.

As we know that the INSTEAD OF trigger will skip the execution of the UPDATE statement because the trigger is fired. Here the execution of the UPDATE statement says:

  • In the UPDATE statement, it will update and set a new string_value of the STUDENT_FIRSTNAME column as Daniel from the HARVARD_UNIVERSITY table with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find a value equal to 20 from the HARVARD_UNIVERSITY table.
    • If the WHERE condition gets a FALSE value then the UPDATE statement will be executed and will be not able to update a new value for the STUDENT_FIRSTNAME column.
    • If the UPDATE statement updates the new value of the STUDENT_FIRSTNAME column in the HARVARD_UNIVERSITY table only when the WHERE condition turns out to be a TRUE value.
Sql server instead of update trigger on view example
Example of SQL Server INSTEAD OF UPDATE trigger on VIEW

We hope that you have understood the subtopic “SQL Server Instead of Update Trigger on View” by using the SQL Server INSTEAD OF trigger on the UPDATE statement on the VIEW of the table by the query. For a better definition, we have used an illustration and explained it in deepness.

Read: Trigger For Delete SQL Server

Can We create Trigger on View SQL Server?

Here we will learn and understand the subtopic “Can we create a trigger on view SQL Server” by using the CREATE VIEW statement on the query, which will be explained with the help of an illustrated example.

There are two kinds of DML TRIGGERS are FOR or AFTER trigger and INSTEAD OF trigger but we can use the view only by using the INSTEAD OF trigger. Contrary to the AFTER type which fires after an event, the INSTEAD OF trigger executes instead of firing the statement. Let’s see an example by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE VIEW VW_USASTATES
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>=20;

CREATE TRIGGER INSTEADOF_USASTATES
ON VW_USASTATES
INSTEAD OF UPDATE
AS
SELECT * FROM USA_STATES
WHERE STATE_ID>26
ORDER BY STATE_ID DESC;

UPDATE VW_USASTATES
SET FULL_NAME='Mike Tyson'
WHERE STATE_ID=29;

In the aforementioned query, a view is created called VW_USASTATES on the SQL STATEMENT by using the CREATE VIEW statement. In the AS clause, the SELECT statement retrieves all records from the USA_STATES table with the WHERE condition.

In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the USA_STATES table.

If the SELECT statement retrieves all records from the USA_STATES table only when the WHERE condition turns out to be TRUE otherwise vice-versa.

In the second query, a trigger is created called INSTEADOF_USASTATES on the VW_USASTATES by using the CREATE TRIGGER statement. Then we have used the INSTEAD OF trigger on the UPDATE statement which will skip the event on the USA_STATES table.

In the AS clause, the SELECT statement retrieves all records from the USA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN operator to find a value greater than 26 from the USA_STATES table and also arranged the STATE_ID column in descending order by using the ORDER BY expression DESC clause.

If the WHERE condition turns out to be TRUE then the SELECT statement retrieves all records from the USA_STATES table otherwise vice-versa.

As the INSTEAD OF trigger has skipped the event of the UPDATE statement. But here execution of the UPDATE statement says:

  • In the UPDATE statement, it will update and set a new string_value of the FULL_NAME column as Mike Tyson with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 29 from the USA_STATES table.
  • If the WHERE condition turns out to be TRUE then the UPDATE statement will update and set a new string_value in the FULL_NAME column only otherwise vice-versa.
SQL Trigger Events example
Can We create Trigger on View SQL Server Example

We hope that you have understood the subtopic “Can We create Trigger on View SQL Server” by using the CREATE TRIGGER and CREATE VIEW statements. For a better explanation, we have used an example and explained it in deepness.

Also, take a look at some more SQL Server tutorials.

We now know SQL Server Trigger On View statement after reading this lesson. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

  • SQL Server Trigger On View
  • SQL Server After Trigger on View
  • SQL Server Trigger View Instead of
  • SQL Server Trigger Alter View
  • SQL Server Instead of Update Trigger on View
  • Can We create Trigger on View SQL Server
  • SQL Server View Triggers on Table