SQL Server Trigger Update with Examples

In this SQL Server tutorial, we will discover and comprehend how to SQL Server trigger update example. 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 Update Example
  • SQL Server Trigger After Insert Update Example
  • SQL Server Trigger After Update Specific Column
  • SQL Server Trigger If Update Multiple Columns
  • SQL Server Before Update Trigger
  • SQL Server For Update vs After Update
  • SQL Server Trigger check If Value Has Changed
  • SQL Server Trigger Insert Update Delete Example

SQL Server Trigger Update Example

In this SQL Server section, we will learn and understand how to use the AFTER TRIGGER method on the table by the query. And which will be explained with the help of an illustrated example.

A store procedure on a database table that is automatically launched or triggered after a SQL transaction and done successfully on the designated table is known as the AFTER UPDATE trigger in a SQL Server. When a time of status change needs to be recorded, it is useful. We can log, audit, or track adjustments using its assistance.

Here is the syntax of the SQL Server AFTER UPDATE triggered by the following query:

SYNTAX:

CREATE TRIGGER [SCHEMA_NAME].[YOUR_TRIGGER_NAME]
ON  YOUR_TABLE_NAME
AFTER UDATE
AS 
BEGIN 
{SQL STATEMENTS}
END;

UPDATE TABLE_NAME
SET COLUMN_NAME= NEW_VALUE
WHERE [CONDITIONS];

Let’s see the syntax explanation:

  • SCHEMA_NAME: it is the name of the schema in which we will create a new trigger in the database. If we want to ignore the schema, we can and it is optional. And it is by default it will create a trigger in the schema in which we are working right now.
  • YOUR_TRIGGER_NAME: it is the name of the trigger that we want to create.
  • YOUR_TABLE_NAME: It is the name of the table where the trigger is applied.
  • SQL STATEMENTS: The SQL STATEMENTS form are the body of the trigger. There are set of operations that can be performed once the AFTER UPDATE TRIGGER is gathered.

We will now see an illustrated example of the SQL Server AFTER UPDATE trigger on the table by the following query:

EXAMPLE:

CREATE TRIGGER TR_AFTERHARVARD
ON HARVARD_UNIVERSITY
AFTER UPDATE 
AS  
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=11;

UPDATE HARVARD_UNIVERSITY 
SET STUDENT_FIRSTNAME='John'
WHERE STUDENT_ID=11;

In the preceding query, we have created a trigger called TR_AFTERHARVARD on the HARVARD_UNIVERSITY table. We have used the AFTER trigger on the UPDATE statement to first execute the update statement and later execute the SQL STATEMENTS which have been used inside the CREATE TRIGGER statement.

Because of the AFTER trigger, the UPDATE statement has been used on the HARVARD_UNIVERSITY table and set the value of the STUDENT_FIRSTNAME column as John in the string_value which is based on the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the value as 10 from the HARVARD_UNIVERSITY table.

Once the trigger has been fired and the UPDATE statement has been executed. Now, we will move to the SELECT statement. In the SELECT statement, it 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 EQUAL TO operator to find the value of 10 from the HARVARD_UNIVERSITY table. Once the value is found from the HARVARD_UNIVERSITY table, the SELECT statement will retrieve all records by the query.

If by the chance the WHERE condition turns out to be FALSE then the SELECT statement query will be executed but it will return empty records for the result set in the output.

Sql Server trigger update example
Example of SQL Server Trigger Update

We hope that you have understood the subtopic “SQL Server Trigger Update Example” from the table by the query. For a better justification, we have used an example and explained it in profundity.

Also, read: SQL Server User Permissions

SQL Server Trigger After Insert Update Example

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

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_AFTERHARVARD
ON HARVARD_UNIVERSITY
AFTER UPDATE, INSERT
AS  
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=6;

UPDATE HARVARD_UNIVERSITY 
SET STUDENT_FIRSTNAME='DAVID'
WHERE STUDENT_ID=11;

INSERT INTO HARVARD_UNIVERSITY 
VALUES(12,'Mike','Tyson','Tyson.mike@mmaboxer.com','Male','2021-07-06','2026-04-07');

As we see in the above query, we have created a trigger called TR_AFTERHARVARD on the HARVARD_UNIVERSITY table. Then we have used the AFTER trigger which is fired on the DML statements i.e; (INSERT and UPDATE). In the AS clause, the SELECT statement execution will be done later once the execution of the UPDATE and INSERT statement execution has been done.

In the second query, the UPDATE statement is used on the HARVARD_UNIVERSITY table to set the value of the STUDENT_FIRSTNAME column as David which is based on the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the value as 10 from the HARVARD_UNIVERSITY table.

So, once the UPDATE statement query has been executed it will automatically execute the SELECT statement. In the third query, the INSERT statement has been used to insert one new record into the HARVARD_UNIVERSITY table.

In the CREATE TRIGGER statement, it has been used the AFTER trigger on any DML statement. Once the DML statement has been executed, the inner states of the CREATE TRIGGER statement as the SELECT statement will be executed afterward.

In the AS clause, the SELECT statement is used to retrieve all records of 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 the value which is greater than or equal to 6 in the HARVARD_UNIVERSITY table.

The SELECT statement is used to retrieve all records from the HARVARD_UNIVERSITY table if the WHERE condition turns out to be TRUE, If by any chance, the WHERE condition turns out to be FALSE then the SELECT statement will retrieve the empty result set for the output while execution time.

Sql Server trigger after insert update example
Example of SQL Server AFTER trigger used on the UPDATE and INSERT statements

We trust that you now fully understand how to use the SQL Server AFTER trigger on the table’s INSERT and UPDATE statements. We used an illustration and went into great detail to provide a better explanation.

Read: Drop stored procedure SQL Server

SQL Server Trigger After Update Specific Column

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

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_AFTERHARVARD
ON HARVARD_UNIVERSITY
AFTER UPDATE
AS  
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=11;

UPDATE HARVARD_UNIVERSITY 
SET GENDER='Male'
WHERE STUDENT_FIRSTNAME='DAVID';

Here we have created a trigger called TR_AFTERHARVARD on the HARVARD_UNIVERSITY table. The AFTER trigger is used and fired on the UPDATE statement of the CREATE TRIGGER statement. In the AS clause, the SELECT statement execution will be done later because the AFTER trigger has been fired.

So, first, the UPDATE statement execution will be done then the SELECT statement will be done. The UPDATE statement is used on the HARVARD_UNIVERSITY table to set the value of the gender as Male which is based on the WHERE condition.

In the WHERE condition, the STUDENT_FIRSTNAME column is used with the EQUAL TO operator to find the string_name as DAVID from the HARVARD_UNIVERSITY table.

Once the string_name value has been found from the HARVARD_UNIVERSITY table then the UPDATE statement will update the string_value as Male for the GENDER column.

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 EQUAL TO operator to find the value as 11 from the HARVARD_UNIVERSITY table.

The SELECT statement will retrieve all records of the HARVARD_UNIVERSITY table if the WHERE condition turns out to be TRUE.

Sql Server trigger after update specific column example
Example of SQL Server AFTER UPDATE trigger used on the specific column

We hope that you have understood the concept of the AFTER trigger on the UPDATE statement which is done on the specific column of the table by the query. For a better illustration, we have used an example and explained it in depth.

Read: Alter view in SQL Server

SQL Server Trigger If Update Multiple Columns

We will learn and understand how to use the SQL Server UPDATE and IF statements used with the AFTER trigger on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_AFTERHARVARD
ON HARVARD_UNIVERSITY
AFTER UPDATE
AS BEGIN
IF(UPDATE (GENDER) OR UPDATE (STUDENT_LASTNAME))
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=11
END;

UPDATE HARVARD_UNIVERSITY
SET STUDENT_LASTNAME='Wastson'
WHERE STUDENT_ID=11;

In the preceding query, we have created a trigger called TR_AFTERHARVARD on the HARVARD_UNIVERSITY table. Then we have fired the AFTER trigger on the UPDATE statement. So once the trigger is fired then it will use on the UPDATE statement.

The UPDATE statement is used on the HARVARD_UNIVERSITY table to set the value of the STUDENT_LASTNAME column as Waston in the new string value. And it is used with the EQUAL TO operator. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the numeric value as 11 from the HARVARD_UNIVERSITY table.

Once the UPDATE statement is executed properly then the SELECT statement inside the BEGIN and END statement will be executed. First, the IF statement is used with the OR condition which will help to update the GENDER and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.

If we try to set a new string or numeric value of the GENDER or STUDENT_LASTNAME column then it will update any one of them by using the UPDATE statement because of the OR condition.

In the BEGIN statement, the SELECT statement retrieves all records of 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 the value as 11 from the HARVARD_UNIVERSITY table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the HARVARD_UNIVERSITY table. And it is closed with the END statement.

Sql Server trigger if update multiple columns example
Example of SQL Server Trigger If Update Multiple Columns

We hope that you have understood how to use the SQL Server AFTER UPDATE trigger on the multiple columns of the table by the query. We have used an example and explained it in depth.

Read: View SQL Server Error Logs

SQL Server Before Update Trigger

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

In SQL Server, there is no BEFORE trigger. An INSTEAD OF trigger can be used to provide matching functionality but the trigger would require executing the UPDATE. Here is an illustrated example of the SQL Server INSTEAD OF UPDATE trigger on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER BEFOREHARVARD
ON HARVARD_UNIVERSITY
INSTEAD OF UPDATE
AS 
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=5;

UPDATE HARVARD_UNIVERSITY
SET STUDENT_FIRSTNAME='Johnny'
WHERE STUDENT_ID=5;

As we see in the above query, we have created a trigger called BEFOREHARVARD on the HARVARD_UNIVERSITY table by using the CREATE TRIGGER statement. We know that there is no such trigger as a BEFORE trigger in the SQL Server.

Therefore, it has utilized the INSTEAD OF trigger on the UPDATE statement, which indicates that it will perform the SELECT statement execution from the CREATE TRIGGER procedure instead of executing the UPDATE statement.

In the AS clause, the SELECT statement will retrieve all records 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 the equal value as 5 from the HARVARD_UNIVERSITY table.

The SELECT statement will retrieve all records from the HARVARD_UNIVERSITY table if the WHERE condition turns out to be TRUE.

If we have used the AFTER trigger on the UPDATE statement then the UPDATE statement will set the value of the STUDENT_FIRSTNAME column as Johnny which is based on the WHERE condition.

In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the value as 5 from the HARVARD_UNIVERSITY table.

Sql Server before update trigger example
Example of SQL Server INSTEAD OF trigger

We hope that you have understood as there is no BEFORE trigger in the SQL Server. An INSTEAD OF trigger has been explained in the SQL Server. For a better illustration, we have used a sample example and described it in depth.

Read: SQL Server logical operators

SQL Server For Update vs After Update

In this SQL Server subtopic tutorial, we will learn and understand the difference between the FOR UPDATE trigger and the AFTER UPDATE trigger which works on the table by the query. And we have used an example and explained it in detail.

The distinction between the AFTER UPDATE and FOR UPDATE triggers is that there isn’t one; both techniques use the same methodology. The DML trigger won’t activate until all of the activities listed in the triggering SQL statement have successfully launched, as indicated by the FOR or AFTER clause.

Before these trigger fires, all referential cascade operations and constraint checks must be victorious. Here is an illustrated example of the SQL Server FOR UPDATE or AFTER UPDATE triggered by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER FORHARVARD
ON HARVARD_UNIVERSITY
FOR UPDATE
AS 
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=5;

UPDATE HARVARD_UNIVERSITY
SET STUDENT_FIRSTNAME='Johnny'
WHERE STUDENT_ID=5;

In the preceding query, we have created a trigger called FORHARVARD on the HARVARD_UNIVERSITY table by using the CREATE TRIGGER statement. The FOR trigger has been used on the UPDATE statement for the execution of the query.

The UPDATE statement is used on the HARVARD_UNIVERSITY table which will update and set the string value as Johnny for the STUDENT_FIRSTNAME column, In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the equal value to 5 from the HARVARD_UNIVERSITY table.

The SELECT statement will retrieve all records from the HARVARD_UNIVERSITY table if the WHERE condition turns out to be TRUE.

But if the WHERE condition turns out to be FALSE then the UPDATE statement will execute an empty result set for the output from the HARVARD_UNIVERSITY table. Once the FOR UPDATE trigger is fired and executed then it will execute the SELECT statement of the CREATE TRIGGER statement.

The SELECT statement will retrieve all records 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 the value which is equal to 5 from the HARVARD_UNIVERSITY table.

Once the WHERE condition is executed then the SELECT statement will bring the result set for the output column.

Sql Server for update vs after update example
Example of SQL Server FOR UPDATE Trigger

We hope that you have understood the difference between the SQL Server FOR UPDATE and the AFTER UPDATE trigger on the table by the query. For a better illustration, we have used a sample and demonstrated it in detail.

Read: SQL Server view order by

SQL Server Trigger check If Value Has Changed

Here we will learn and understand how to use the SQL Server AFTER TRIGGER to check if the value has been changed in the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTERTRIGGER
ON HARVARD_UNIVERSITY
AFTER UPDATE
AS
BEGIN

    SELECT * INTO #deleted FROM deleted
    SELECT * INTO #inserted FROM inserted

    ALTER TABLE #deleted DROP COLUMN GENDER
    ALTER TABLE #inserted DROP COLUMN GENDER

    select * from #deleted
    except 
    select * from #inserted
END;

update HARVARD_UNIVERSITY SET STUDENT_LASTNAME='Boslin'
WHERE STUDENT_ID=4;

update HARVARD_UNIVERSITY set STUDENT_FIRSTNAME='Downey'
WHERE STUDENT_ID=5;

in the above query, a new trigger is created called AFTERTRIGGER on the HARVARD_UNIVERSITY table. Then we have used the AFTER trigger on the UPDATE statement which means that it will first execute the UPDATE statement then it will execute the SQL statement which is being used in the BEGIN and END statements.

In the BEGIN statement, we have used the SELECT INTO statement to delete data from the DELETE table and transfer it to the DELETE pseudo table. The same method goes to insert into the INSERT table and then transfer it to the INSERT pseudo table.

The ALTER TABLE statement has been used on the DELETED pseudo table to drop the GENDER column. And it has been used the same on the INSERTED pseudo table to drop the GENDER column from the database.

In the UPDATE statement, it is used on the HARVARD_UNIVERSITY table and set the value of the STUDENT_LASTNAME column as Boslin which is based on the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the equal value as 4 from the HARVARD_UNIVERSITY table.

If the WHERE condition turns out to be TRUE then the UPDATE statement will update the STUDENT_LASTNAME column as Boslin.

The same UPDATE statement has been used again on the HARVARD_UNIVERSITY table but on a different column which is The STUDENT_FIRSTNAME column. This column has been set new value as Downey which is based on the WHERE condition.

In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find and get the equal value as 5 from the HARVARD_UNIVERSITY table. The UPDATE statement will retrieve all records from the HARVARD_UNIVERSITY table if the WHERE condition turns out to be TRUE.

If the WHERE turns out to be FALSE then the UPDATE statement will retrieve the empty records for the result set.

SQl  Server Trigger check if Value has Changed
Example of SQL Server Trigger check If Value Has Changed

We hope that you have understood the subtopic “SQL Server Trigger check If Value Has Changed” by using the AFTER TRIGGER on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: Comparison Operators in SQL Server

SQL Server Trigger Insert Update Delete Example

In this SQL Server subtopic tutorial, we will learn and understand how to use the SQL Server FOR TRIGGER for the INSERT, UPDATE and DELETE statements of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE OR ALTER TRIGGER TR_IUDNesting
ON HARVARD_UNIVERSITY
FOR INSERT, UPDATE, DELETE
AS
DECLARE @Operation_Statement VARCHAR(15) 
IF EXISTS (SELECT 0 FROM inserted)
BEGIN
   IF EXISTS (SELECT 0 FROM deleted)
   BEGIN 
      SELECT @Operation_Statement = 'UPDATE'
   END ELSE
   BEGIN
      SELECT @Operation_Statement = 'INSERT'
   END
END ELSE 
BEGIN
   SELECT @Operation_Statement= 'DELETE'
END 
PRINT @Operation_Statement;

INSERT INTO HARVARD_UNIVERSITY
(STUDENT_ID) VALUES (0);
 
UPDATE HARVARD_UNIVERSITY
SET STUDENT_FIRSTNAME = 'Chris'
WHERE STUDENT_ID = 1;
 
DELETE FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID = 8;

In this preceding query, a new trigger is created called TR_IUDNesting on the HARVARD_UNIVERSITY table by using the CREATE OR ALTER TRIGGER statement. We have used the FOR TRIGGER on the DML statement i,e; (INSERT, UPDATE, DELETE) statements.

In the ALIAS clause, we have used the DECLARE keyword to declare a new value as OPERATION_STATEMENT with data type VARCHAR(15).

Then we have used the IF EXISTS statements for checking the condition of the parameters. The SELECT statement will retrieve 0 from the INSERTED table a.k.a dummy table. The same method goes for the DELETE statement for the DELETED table.

In the BEGIN statement, we have set the declaration of the OPERATION_STATEMENT as UPDATE, INSERT and DELETE which will come as an output string value for all three statements. And in the end, we have closed them by END statement.

In the CREATE OR ALTER TRIGGER statement, we have used the PRINT function to print the value of OPERATION_STATEMENT as INSERT, UPDATE or DELETE while synchronized.

Once the FOR TRIGGER is fired, we can execute the INSERT, DELETE and UPDATE statement by the same printed string value. In the INSERT INTO statement, we have inserted a new record in the STUDENT_ID column as 0 on the HARVARD_UNIVERSITY table.

The UPDATE statement will update and set the value of the STUDENT_FIRSTNAME column as Chris with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the value as 1 from the HARVARD_UNIVERSITY table.

Once the WHERE condition gets TRUE then the UPDATE statement will set a new value for that column.

The DELETE statement will one records 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 of 8 in the STUDENT_ID column. Once the value gets all rows with deleted from the HARVARD_UNIVERSITY table.

Sql Server trigger insert update delete example
Example of SQL Server FOR trigger for INSERT, DELETE and UPDATE statements

We hope that you have understood how to use the SQL Server FOR TRIGGER on the UPDATE, DELETE and INSERT statements of the table by the query. For a better instance, we have used a sample example and explained it in depth.

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

As a result, we learned how to use the SQL Server Trigger Update Example in this article. We also covered and learned a variety of examples to help you understand the concept better. The full list of topics we will cover is given below.

  • SQL Server Trigger Update Example
  • SQL Server Trigger After Insert Update Example
  • SQL Server Trigger After Update Specific Column
  • SQL Server Trigger If Update Multiple Columns
  • SQL Server Before Update Trigger
  • SQL Server For Update vs After Update
  • SQL Server Trigger check If Value Has Changed
  • SQL Server Trigger Insert Update Delete Example