SQL Server Trigger Before Update

In this SQL Server tutorial, we will learn and comprehend how to use the SQL Server Trigger Before Update statement. We will discuss and learn several instances to assist you in better understanding the concept. The full list of topics we will cover is given below.

• SQL Server Trigger Before Update
• SQL Server Trigger Before Update Old New Value
• SQL Server Trigger Instead of Update
• SQL Server Trigger Update Before And Insert Values
• SQL Server Trigger Before Insert Update
• SQL Server Trigger Get Value Before Update
• SQL Server Trigger Instead of Insert Update
• SQL Server Trigger Update

Also, check the related SQL Server tutorial: Trigger to insert data in another table in SQL Server

SQL Server Trigger Before Update

Here we will learn and understand how to use the SQL Server INSTEAD OF trigger on the UPDATE statement of the table by the query. And which will be explained with the help of an illustrated example.

An SQL trigger that is handled “instead of” a SQL UPDATE, DELETE or INSERT statement is known as an INSTEAD OF trigger. An INSTEAD OF trigger may only be defined on a view and not a table, unlike SQL BEFORE and AFTER triggers. Let’s see an illustrated example of the SQL Server INSTEAD OF trigger used with the UPDATE statement on the table by the following query:

SYNTAX:

CREATE TRIGGER YOUR_TRIGGER_NAME
ON YOUR_TABLE_NAME
INSTEAD OF UPDATE
AS 
[ SQL STATEMENTS];

UPDATE YOUR_TABLE_NAME
AS COLUMN_NAME= NEW_VALUE
WHERE [CONDITIONS];

We will see an example of the SQL Server INSTEAD OF trigger on the UPDATE statement from the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_UPDATE
ON CANADA_STATES
INSTEAD OF UPDATE
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID >=10;

UPDATE CANADA_STATES
SET FULL_NAME='Phoebe Buffay'
WHERE STATE_ID=24;

In this preceding query, we have created a trigger called INSTEADOF_UPDATE on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have used the INSTEAD OF trigger on the UPDATE statement which means that it will skip the execution of the UPDATE statement instead it will view and execute the SQL STATEMENT which is used inside the CREATE TRIGGER statement.

In the AS clause, the SELECT statement is used to retrieve all records from the CANADA_STATES table which is used 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 10 from the CANADA_STATES table.

If the SELECT statement retrieves all records from the CANADA_STATES table only when the WHERE condition turns out to be TRUE. But the SELECT statement retrieves empty records set for the output from the CANADA_STATES table if the WHERE condition gets a FALSE value.

Because of the INSTEAD OF trigger, the execution of the UPDATE statement is executed but it didn’t make any effect on the CANADA_STATES table. Here, the execution of the UPDATE statement says that:

• The UPDATE statement is used to update and set a string_value of the FULL_NAME column as Phoebe Buffay which is based on the WHERE condition.
  • In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 24 from the CANADA_STATES table.
  • If the WHERE condition turns out to be TRUE then the UPDATE statement will update the value of that column.
  • If the UPDATE statement updates none value of that column then it means that the WHERE condition gets a FALSE value.
• As we know, the INSTEAD OF trigger will skip the execution of the DML STATEMENTS but we can view the execution of the SQL STATEMENTS which are used inside the CREATE TRIGGER statement.

We hope that you have understood the subtopic “SQL Server Trigger Before Update” by using the INSTEAD OF trigger on the table by the query. For a better description, we have used an illustration and clarified it in depth.

Read: Indexed views in SQL Server

SQL Server Trigger Before Update Old New Value

In this subtopic, we will learn and understand how to use the SQL Server INSTEAD OF the trigger used with the UPDATE statement on the table by the following query:

EXAMPLE:

USE sqlserverguides;

CREATE TRIGGER TR_INSTEADOF_UPDATE
ON CANADA_STATES
INSTEAD OF INSERT 
AS 
UPDATE CANADA_STATES
SET FULL_NAME='Erich Heather'
WHERE STATE_ID=11;

INSERT INTO CANADA_STATES 
VALUES(25,'Dave Batista','Nova Scotia',4521);

In this preceding query, we have used the CREATE TRIGGER statement to create a trigger as TR_INSTEADOF_UPDATE on the CANADA_STATES table. Then the INSTEAD OF trigger is used on the INSERT statement which means that it will skip the DML statement efforts on the table but the execution will be there. But it will execute the SQL STATEMENTS which are used inside the CREATE TRIGGER statement as the INSTEAD OF trigger is triggered.

In the AS clause, the UPDATE statement has been used to update and set a new string_value of the FULL_NAME column as Erich Heather on the CANADA_STATES table. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value that is equal to 11 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the UPDATE statement will be able to update a new value for the FULL_NAME column of the CANADA_STATES table. But if the UPDATE statement is unable to update no record into the CANADA_STATES table only when the WHERE condition gets a FALSE value.

As we see the INSTEAD OF trigger is triggered on the DML statements, then it means that it will skip the execution of the DML statement. Here the execution of the INSERT INTO statement on the CANADA_STATES table says that:

• In the INSERT INTO statement, it is used to insert a new record into the CANADA_STATES table.

We hope that you have understood the subtopic “SQL Server Trigger Before Update Old New Value” by using the INSTEAD OF trigger on the UPDATE statement from the table. For a better understanding, we have used an example and explained it in depth.

Read: Disable Trigger in SQL Server

SQL Server Trigger Instead of Update

We will learn and understand how to use the SQL Server INSTEAD OF the trigger used with the UPDATE statement on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_UPDATE
ON USA_STATES
INSTEAD OF UPDATE
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID >=10;

UPDATE USA_STATES
SET FULL_NAME='John Cena'
WHERE STATE_ID=20;

As we see in the above query, a trigger is created called INSTEADOF_UPDATE on the USA_STATES table by using the CREATE TRIGGER statement. Then, we used the INSTEAD OF trigger on the UPDATE statement which means that the INSTEAD OF trigger is used to skip the DML statement execution and the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be executed.

In the AS clause, we have used the SELECT statement to retrieve all records from the USA_STATES table based on 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 10 from the USA_STATES table.

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

As the execution of the UPDATE statement is executed but it will not bring any effect on the USA_STATES table because of the INSTEAD OF trigger. Here the 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 “John Cena” which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 20 from the USA_STATES table.
  • The WHERE condition gets a TRUE value then the UPDATE statement will update a new value for that column of the table.
  • But as the WHERE condition turns out to be FALSE then the UPDATE statement will update no record for the USA_STATES table.

We hope that you have understood the subtopic “SQL Server Trigger Instead of Update” by using the SQL Server INSTEAD OF trigger on the UPDATE statement of the table by the query. We have used an example and explained it in depth.

Read: Trigger For Delete SQL Server

SQL Server Trigger Update Before And Insert Values

We will learn and understand how to use the SQL Server INSTEAD OF trigger on the INSERT and UPDATE statement of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_UPDATINSERTHARVARD
ON HARVARD_UNIVERSITY
INSTEAD OF UPDATE,INSERT 
AS
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID >=10;

INSERT INTO HARVARD_UNIVERSITY 
VALUES(16,'Chris','Evans','evans.chris@gmail.com','Male','2022-04-08','2027-03-12');

UPDATE HARVARD_UNIVERSITY
SET STUDENT_FIRSTNAME='Steve'
WHERE STUDENT_ID=13;

In this preceding query, we have used the CREATE TRIGGER statement to create a trigger called TR_UPDATEINSERTHARVARD on the HARVARD_UNIVERSITY table. Then we have used the INSERTED OF clause on the UPDATE and INSERT statements which means that it will skip the execution of the DML STATEMENTS on the HARVARD_UNIVERSITY table and it will execute the SQL STATEMENT for the result set.

In the AS clause, the SELECT statement is used to retrieve all records from the HARVARD_UNIVERSITY table based on 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 WHERE condition gets a TRUE value then the SELECT statement will retrieve all records from the HARVARD_UNIVERSITY table. If the SELECT statement will retrieve an empty record set from the HARVARD_UNIVERSITY table, it means that the WHERE condition is not able to find these values from the STUDENT_ID column of the HARVARD_UNIVERSITY table.

Once the INSTEAD OF TRIGGER is fired, it will skip the DML statements over the execution on the table. Here is the execution of the INSERT and UPDATE statement after the INSTEAD OF trigger is triggered:

• In the INSERT INTO statement, it will insert a new record into the HARVARD_UNIVERSITY table.
• In the UPDATE statement, it will update and set a new string_value of the STUDENT_FIRSTNAME column as Steve in the HARVARD_UNIVERSITY table which is used 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 13 from the HARVARD_UNIVERSITY table.
• if the WHERE condition turns out to be TRUE then the UPDATE statement will update a new value for that STUDENT_FIRSTNAME column in the HARVARD_UNIVERSITY table.
• But the UPDATE statement isn’t unable to set a new value for the STUDENT_FIRSTNAME column only when the WHERE condition gets a FALSE value.

We hope that you have understood the subtopic “SQL Server Trigger Update Before And Insert Values” by using the SQL Server INSTEAD OF trigger on the table by the query. For a better description, we have used an example and explained it in depth.

Read: SQL Server Trigger After Insert Update

SQL Server Trigger Before Insert Update

Here we will learn and understand how to use the SQL Server INSTEAD OF trigger on the INSERT and UPDATE statements of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_INSERTUPDATE
ON USA_STATES
INSTEAD OF INSERT, UPDATE
AS 
SELECT * FROM USA_STATES
ORDER BY STATE_ID DESC;

INSERT INTO USA_STATES
VALUES(26,'Denver',62584,'Adam Jones','Male');

UPDATE USA_STATES
SET FULL_NAME='Phoebe Buffay'
WHERE STATE_ID=24;

In this preceding query, a trigger is created called INSTEADOF_INSERTUPDATE on the USA_STATES table by using the CREATE TRIGGER statement. And then we have used the INSTEAD OF trigger on the INSERT and UPDATE statement which means that it will skip the DML statement instead and will return the SQL STATEMENTS from the USA_STATES table.

In the AS clause, the SELECT statement is used to retrieve all records from the USA_STATES table which is used with the ORDER BY clause. In the ORDER BY clause, the STATE_ID column is used with the DESC keyword to arrange the columns in descending order. Once the column is arranged in descending order then the SELECT statement will retrieve all records from the USA_STATES table for the result set.

Here, the INSTEAD OF trigger is triggered which means that the INSERT and UPDATE statement will be executed but it will not bring any effect on the USA_STATES table. Here the execution of the INSERT and UPDATE statement says:

• In the INSERT statement, it is used to insert a new record into the USA_STATES table.
• In the UPDATE statement, it’s used to update and set a new string_value of the FULL_NAME column as PHOEBE BUFFAY in the USA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 24 from the USA_STATES table.
  • If the WHERE condition gets a FALSE value then the UPDATE statement will update no record for that column.
  • The UPDATE statement will be able to update a new value for that column only when the WHERE condition turns out to be TRUE.

We hope that you have understood the subtopic “SQL Server Trigger Before Insert Update” by using the SQL Server INSTEAD OF trigger on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: SQL Server logical operators

SQL Server Trigger Get Value Before Update

We will study how to use the SQL Server INSTEAD OF trigger and how it works with the query’s UPDATE statement for the table. And that will be clarified with the aid of an example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_UPDATEMIT
ON MIT_UNIVERSITY
INSTEAD OF UPDATE AS
SELECT * FROM MIT_UNIVERSITY
ORDER BY STUDENT_FIRSTNAME DESC;

UPDATE MIT_UNIVERSITY
SET STUDENT_FIRSTNAME='Damon'
WHERE STUDENT_ID=9;

First, we have created a trigger TR_UPDATEMIT on the MIT_UNIVERSITY table by using the CREATE TRIGGER statement. Then the INSTEAD OF trigger is used on the UPDATE statement which means that it will trigger the SQL STATEMENT inside the CREATE TRIGGER statement and it will skip the UPDATE statement and its execution.

In the AS clause, the SELECT statement will retrieve all records from the MIT_UNIVERSITY table which is used with the ORDER BY clause. In the ORDER BY clause, the STUDENT_FIRSTNAME column is used with the DESC keyword to arrange the records in descending order.

So, when the order is arranged in the descending order by the ORDER BY clause then the SELECT statement will retrieve all records from the MIT_UNIVERSITY table for the result set.

The UPDATE statement execution will be executed but it will not bring any effect to the MIT_UNIVERSITY table because the INSTEAD OF trigger is fired. Here is 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 Damon in the MIT_UNIVERSITY table which is used 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 9 from the MIT_UNIVERSITY table.
  • If the WHERE condition turns out to be TRUE then the UPDATE statement will update and set a new string value for that column in the MIT_UNIVERSITY table.
  • If the UPDATE statement will not update and set no new record for that column then it means the WHERE condition gets a FALSE value.

We hope that you have understood the subtopic “SQL Server Trigger Get Value Before Update” by using the SQL Server INSTEAD OF trigger on the table by the query. For a better understanding, we have used an example and explained it in depth.

Read: SQL Server Drop Trigger If Exists

SQL Server Trigger Instead of Insert Update

Here we will learn and understand how to use the SQL Server INSTEAD OF INSERT, UPDATE trigger on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_UPDATE
ON CANADA_STATES
INSTEAD OF UPDATE,INSERT
AS 
SELECT * FROM CANADA_STATES
ORDER BY STATE_ID DESC;

UPDATE CANADA_STATES
SET FULL_NAME='Henry Cavil'
WHERE STATE_ID=23;

INSERT INTO CANADA_STATES
VALUES(25,'Thor Odinson','California','Male');

In the aforementioned query, we have created a trigger called TR_UPDATE on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have used the INSTEAD OF trigger which means that it will skip the DML STATEMENTS (INSERT, DELETE, UPDATE) and the SQL STATEMENT will be triggered.

In the AS clause, we have used the SELECT statement to retrieve all records from the CANADA_STATES table which is used with the ORDER BY clause. In the ORDER BY clause, the STATE_ID column is used with the DESC keyword which means that it will arrange the records in descending order from the CANADA_STATES table.

Here the execution of the DML STATEMENTS has been triggered by execution which means that it will be executed but it will not bring any effect on the CANADA_STATES table. Here the execution of the DML STATEMENTS says:

• In the UPDATE statement, it is used to update and set a new value for the FULL_NAME column as Henry Canvil from the CANADA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 23 from the CANADA_STATES table.
  • If the WHERE condition turns out to be TRUE then the UPDATE statement will be able to update and set a new value for the CANADA_STATES table.
  • If the UPDATE statement set no new value for that column in the CANADA_STATES table only when the WHERE condition turns out to be FALSE.
• In the INSERT INTO statement, it is used to insert a new record into the CANADA_STATES table.

We hope that you have understood how to use the SQL Server INSTEAD OF TRIGGER used with the INSERT and UPDATE statements on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: Instead of Trigger In SQL Server

SQL Server Trigger Update

We will learn and understand how to use the SQL Server AFTER trigger on the UPDATE statement on the table by the query. And which will be explained with the help of syntax and illustrated examples.

In SQL, an AFTER UPDATE Trigger is a stored procedure on a database table that is automatically invoked or triggered following the successful completion of an UPDATE action on the given table. The UPDATE statement, for the uninitiated, is used to change data in already-existing rows in a data database.

Let’s see the syntax of the SQL Server AFTER trigger on the UPDATE statement of the table by the following query:

SYNTAX:

CREATE TRIGGER YOUR_TRIGGER_NAME 
ON YOUR_TABLE_NAME
AFTER [ INSERT | DELETE | UPDATE]
AS 
[ SQL STATEMENTS];

Here is an illustrated example of the SQL Server AFTER trigger on the UPDATE statement of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_UPDATE
ON CANADA_STATES
AFTER UPDATE
AS 
SELECT * FROM CANADA_STATES
ORDER BY STATE_ID DESC;

UPDATE CANADA_STATES
SET FULL_NAME='Henry Cavil'
WHERE STATE_ID=23;

The CREATE TRIGGER statement is used to create a trigger called TR_UPDATE on the CANADA_STATES table, as can be seen in the aforementioned query. When the AFTER trigger fires, the UPDATE statement will be executed and update the table’s record. This is because the AFTER trigger is utilized on the UPDATE statement.

In the UPDATE statement, it will update and set a string_value as Henry Cavil for the FULL_NAME column which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 23 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the UPDATE statement will update a new value for that column in the CANADA_STATES table. But if the WHERE condition gets a FALSE value then the UPDATE statement will update no record for that column in the CANADA_STATES table.

In the AS clause, the SELECT statement will retrieve all records from the CANADA_STATES table which is used with the ORDER BY clause.

In the ORDER BY clause, the STATE_ID column is used with the DESC keyword which will arrange the records in descending order. Once the column is arranged the descending order then the SELECT statement will retrieve all records from the USA_STATES table.

We hope that you have understood how to use the SQL Server AFTER trigger used with the INSERT and UPDATE statement of the table by the query. For a better illustration, we have used an example and demonstrated it in depth.

You may also like to read the following SQL Server tutorials.

• SQL Server String_agg
• SQL Server Date Format
• SQL Server Datetime functions
• Alter view in SQL Server
• How to Debug an SQL Server Trigger
• SQL Server Trigger to Increment Id

In this SQL Server tutorial, we now know SQL Server Trigger Before Update 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 Before Update
• SQL Server Trigger Before Update Old New Value
• SQL Server Trigger Instead of Update
• SQL Server Trigger Update Before And Insert Values
• SQL Server Trigger Before Insert Update
• SQL Server Trigger Get Value Before Update
• SQL Server Trigger Instead of Insert Update
• SQL Server Trigger Update