SQL Server Trigger After Insert Update

In this SQL Server tutorial, we will learn and comprehend how SQL Server Trigger After Insert Update statement works. To help you comprehend the idea better, we will also talk about and learn various examples. The complete list of subjects we will address is provided below.

• SQL Server Trigger After Insert Update
• SQL Server Trigger After Insert Update Example
• SQL Server Trigger After Insert Update Delete
• SQL Server Trigger After Update Inserted Table
• SQL Server Trigger After Insert Update Another Table
• SQL Server Trigger After Insert Update Specific Column
• SQL Server Trigger After Insert Update Same Table
• SQL Server Trigger After Insert Update Timestamp

SQL Server Trigger After Insert Update

In this SQL Server section, we will learn and understand how to use the SQL Server AFTER trigger used with the INSERT and UPDATE statements of the table by the query. And which is explained with the help of an illustrated example.

The SQL Server AFTER trigger is executed after the firing statement end with the INSERT, DELETE and UPDATE statements. We can also use the SQL Server FOR trigger whose method is the same as the AFTER trigger.

Here is the syntax of the SQL Server AFTER trigger used with the INSERT and UPDATE statements by the following query:

SYNTAX:

USE YOUR_DATABASENAME;

CREATE TRIGGER YOUR_TRIGGER_NAME
ON YOUR_TABLE_NAME
WITH [OPTIONS]
AFTER  { INSERT | UPDATE | DELETE } 
AS 
{ SQL STATEMENTS } 

INSERT INTO YOUR_TABLE_NAME
VALUES (VALUES_1, VALUES_N);

UPDATE YOUR_TABLE_NAME 
SET COLUMN_NAME= NEW_VALUE
WHERE [CONDITIONS];

The syntax explanation:

• WITH [OPTIONS]: In this argument, we can specify the additional options for the creation of a trigger.
• AFTER: It indicates that when a trigger is fired when an event happens like an insert, delete and update statements.
• [ INSERT | DELETE | UPDATE ]: These are the DML events that cause a fire.

Let’s see an illustrated example of the SQL Server AFTER Trigger used with the INSERT and UPDATE statements by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_AFTERINSERTUPDATE
ON USA_STATES
AFTER INSERT, UPDATE
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID >15;

INSERT INTO USA_STATES VALUES(22,'Mimai',89254,'Chirs Pratt','Male');

UPDATE USA_STATES
SET STATE_NAME='California'
WHERE STATE_ID=21;

As we see in the above query, we have created a trigger called TR_AFTERINSERTUPDATE on the USA_STATES table. Then, we have used the AFTER trigger on the INSERT and UPDATE statement which means that once the trigger is fired then the DML events will be executed i.e; (INSERT, DELETE and UPDATE).

In the AS clause, the SELECT statement execution will be executed once the INSERT INTO statement inserts some new record into the USA_STATES table. And the UPDATE statement sets a new value for the column which is based on the WHERE condition.

In the INSERT statement, we have inserted one record into the USA_STATES table whose record will be given after the STATE_ID column as a 21 value. And in the UPDATE statement, we have to set and update the new string_value as California for the STATE_NAME column 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 operator to find a value equal to 21 from the USA_STATES table. The UPDATE statement will update one record from the USA_STATES table if the WHERE condition turns out to be TRUE. If the WHERE condition turns out to be FALSE then the UPDATE statement will update the empty record from the USA_STATES table.

Once the execution of the INSERT, the UPDATE statement is executed then the execution of the SELECT statement will be started. Here is the SELECT statement execution says in the AS clause after the trigger is fired:

• In the SELECT statement, it will retrieve all records from the USA_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 greater value that is greater than 15 from the STATES_USA 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 will retrieve an empty record set for the output if the WHERE condition turns out to be FALSE.

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

Read: Introduction to SQL Server Trigger

SQL Server Trigger After Insert Update Example

We will learn and understand how to use the SQL Server AFTER trigger on the INSERT and UPDATE statements on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

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

INSERT INTO HARVARD_UNIVERSITY 
VALUES(13,'Chirs','Pratt','pratt.chris23@gmail.com','Male','2021-03-04','2027-04-06');

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

In this preceding query, we have created a trigger called INSERT UPDATE on the HARVARD_UNIVERSITY table. Then we used the AFTER trigger on the INSERT and UPDATE statements. So it means that once the AFTER trigger is fired the INSERT and UPDATE statements will be executed after that the SQL STATEMENTS will be executed which is used inside the AS clause.

In the INSERT INTO statement, it will insert one new record into the HARVARD_UNIVERSITY table. In the UPDATE statement, it will update and set a new string value as David for the STUDENT_FIRSTNAME column which is based on the WHERE condition.

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

Here is the execution of the SELECT statement says after the trigger is fired which is given below:

• In the SELECT statement, it will retrieve all records from the HARVARD_UNIVERSITY table which is used 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 8 from the HARVARD_UNIVERSITY table.
  • If the WHERE condition gives a TRUE value then the SELECT statement will retrieve all records from the HARVARD_UNIVERISTY table. But if the WHERE condition turns out to be FALSE then the SELECT statement will retrieve an empty record set for the output.

We hope that you have understood the subtopic “SQL Server Trigger After Insert Update” by using the AFTER trigger on the INSERT and UPDATE statements of the table by the query. For a better example, we have used an example and explained it in depth.

Also, check: SQL Server Date Format

SQL Server Trigger After Insert Update Delete

Here we will learn and understand how to use the SQL Server AFTER trigger used with the INSERT, DELETE and UPDATE statement on the table by the query. And which is explained with the help of a signified example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSERTUPDATE_HARVARD
ON HARVARD_UNIVERSITY
AFTER INSERT, UPDATE, DELETE
AS 
SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID >=9;

INSERT INTO HARVARD_UNIVERSITY 
VALUES(14,'David','Depp','depp.david@gmail.com','Male','2021-02-04','2024-04-06');

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

DELETE FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=8;

As the above query explains, a trigger is created called INSERTUPDATE_HARVARD on the HARVARD_UNIVERSITY table. Then we used the AFTER keyword on the INSERT, DELETE and UPDATE statements.

Once the AFTER trigger is fired, we will execute the INSERT INTO, UPDATE, and DELETE statements first then we will execute the SQL STATEMENT which is used inside the CREATE TRIGGER statement.

In the INSERT statement execution, we have inserted a new record into the HARVARD_UNIVERSITY table. Then the UPDATE statement is used to update and set the STUDENT_FIRSTNAME column as KEVIN which is based on 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 execute and update the value of the column.

In the DELETE statement, it will delete the record from the HARVARD_UNIVERSITY table which is based on the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find a value equal to 8 from the HARVARD_UNIVERSITY table. If the WHERE condition finds the value then it will delete the value by using the DELETE statement.

After the trigger is fired, then the SELECT statement execution will be executed and it says:

• 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 GREATER THAN OR EQUAL TO operator to find a value greater than or equal to 9 from the HARVARD_UNIVERSITY table.
  • If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve an empty record set from the HARVARD_UNIVERSITY table.
  • But if the WHERE condition gets a FALSE value it means that there is no value as per the condition. Then the SELECT statement will retrieve the empty record set from the HARVARD_UNIVERSITY table.

We hope that you have understood how to use the SQL Server AFTER trigger with the INSERT, DELETE and UPDATE statements from the table by the query. For a better illustration, we have used an example and explained it in deepness.

Read: SQL Server First Day Of Month

SQL Server Trigger After Update Inserted Table

In this SQL Server section, we will learn and understand how to use the SQL Server AFTER trigger used with the UPDATE statement into the INSERTED pseudo table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSERTUPDATE_HARVARD
ON HARVARD_UNIVERSITY
AFTER UPDATE
AS 
SELECT * FROM inserted
WHERE STUDENT_ID >12;

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

In this preceding query, we have created a trigger called INSERTUPDATE_HARVARD on the HARVARD_UNIVERSITY table. We have used the AFTER trigger on the UPDATE statement which means that once the trigger is fired then the execution of the UPDATE statement will be done after that the SQL STATEMENTS which are used inside the CREATE TRIGGER statement will be executed.

In the UPDATE statement, it will update and set the value of the STUDENT_FIRSTNAME column as Dwayne with help of the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the value equal to 13 from the HARVARD_UNIVERSITY table.

The UPDATE statement will be able to set value only when the WHERE condition gets TRUE. If the WHERE condition gets FALSE then the UPDATE statement will execute with no update.

Once the AFTER trigger is fired and executed, the SELECT statement will retrieve all records from the INSERTED pseudo table with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN operator to find a value greater than 12 from the INSERTED pseudo table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the INSERTED pseudo table. But if the WHERE condition is FALSE then the SELECT statement will retrieve the empty record set for the output.

We hope that you have understood how to use the SQL Server AFTER UPDATE trigger on the INSERTED pseudo table by the query. For a better illustration, we have used a demonstrated example and explained it in depth.

Read: SQL Server Datetime functions

SQL Server Trigger After Insert Update Another Table

We will learn and understand how to use the SQL Server AFTER trigger used with the INSERT and UPDATE statements on another table by the following query:

EXAMPLE:

USE sqlserverguides;

CREATE TRIGGER AFTER_TIMESTAMPUSA
ON USA_ALPHABET_COMPANY
AFTER INSERT, UPDATE
AS 
SELECT INSERTED.EMPLOYEE_ID AS INSERTED_ID,
INSERTED.EMPLOYEE_FIRSTNAME AS INSERTED_FIRSTNAME, 
INSERTED.EMPLOYEE_LASTNAME AS INSERTED_LASTNAME,
INSERTED.EMPLOYEE_ADMITDATE AS INSERTED_DATE
FROM INSERTED;
SELECT DELETED.EMPLOYEE_ID AS DELETED_ID,
DELETED.EMPLOYEE_FIRSTNAME AS DELETED_FIRSTNAME, 
DELETED.EMPLOYEE_LASTNAME AS DELETED_LASTNAME,
DELETED.EMPLOYEE_ADMITDATE AS DELETED_DATE
FROM DELETED;

INSERT INTO USA_ALPHABET_COMPANY
VALUES(12,'Kevin','Hart','2021-02-03');

UPDATE USA_ALPHABET_COMPANY
SET EMPLOYEE_FIRSTNAME='Dwayne'
WHERE EMPLOYEE_ID=10;

In the preceding query, we have created a trigger called AFTER_TIMESTAMPUSA for the USA_ALPHABET_COMPANY table by using the CREATE TRIGGER statement. Then we used the AFTER trigger on the INSERT and UPDATE statements.

In the INSERT statement, it will insert one record into the USA_ALPHABET_COMPANY table.

In the UPDATE statement, it will update and set a new string_value for the EMPLOYEE_FIRSTNAME column as Dwayne which is based on the WHERE condition. In the WHERE condition, the EMPLOYEE_ID column is used with the EQUAL TO operator to find a value equal to 10 from the USA_ALPHABET_COMPANY table.

In the WHERE condition turns out to be TRUE then the UPDATE statement will execute and update the record of that column. If the WHERE condition turns out to be FALSE then the UPDATE statement will be executed and no update action will be done.

In the AS clause, the SELECT statement will retrieve all records of the USA_ALPHABET_COMPANY table into the INSERTED pseudo table with the new column_name. And it is done by the ALIAS clause with the AS keyword.

This means that the new record is inserted into the USA_ALPHABET_COMPANY table and the same record will be also inserted into the INSERTED pseudo table.

The same method of the ALIAS clause with the AS keyword has been used in the DELETED pseudo table by using the SELECT statement. This means that when we have used the UPDATE statement to update the record of that column, the whole record of the USA_ALPHABET_COMPANY table will be added to the DELETED pseudo table.

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

Read: Pass parameters to view in SQL Server

SQL Server Trigger After Insert Update Specific Column

In this SQL Server, we will learn and understand how to use the SQL Server AFTER trigger used with the INSERT and UPDATE statement on the specific column of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

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

INSERT INTO HARVARD_UNIVERSITY VALUES 
(15, 'Robert', 'Downey Jr','Downey.jr.robert@gmail.com','Male','2021-05-06','2025-07-09');

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

In this preceding query, we have created a trigger called INSERT_UPDATE_HARVARD on the HARVARD_UNIVERSITY table. Once the AFTER trigger is fired on the UPDATE and INSERT statement then it means that it will execute first then only the SQL STATEMENTS will execute after that.

We have used the INSERT statement to insert one new record into the HARVARD_UNIVERSITY table. Then, we used the UPDATE statement to update and set a new value as Johnny for the STUDENT_FIRSTNAME column which is based on the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find a value equal to 14 from the HARVARD_UNIVERSITY table.

Once the INSERT and UPDATE statement is executed, the SELECT statement used inside the CREATE TRIGGER statement will be executed. And it will bring the record for the result set.

As the SELECT statement says that it will retrieve all records from the HARVARD_UNIVERSITY table which is used 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 14 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. But if the SELECT statement brings the empty record set for the output then the WHERE condition is a FALSE value.

We hope that you have understood how to use the SQL Server AFTER trigger which was used with the INSERT and UPDATE statement for the specific column of the table. For a better illustration, we have used an example and explained it in depth.

Read: Drop stored procedure SQL Server

SQL Server Trigger After Insert Update Same Table

Here we will learn and understand how to use the SQL Server AFTER trigger used with the INSERT and UPDATE statement on the same table by two different examples which are shown below:

AFTER INSERT TRIGGER EXAMPLE:

USE SQLSERVERGUIDES;

ALTER TRIGGER STATESOF_USA
ON USA_STATES
AFTER INSERT 
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>=20;

INSERT INTO USA_STATES VALUES
(24,'North Dakota',45580,'Jennifier Heather','Female');

As we see in the above query, we have created a trigger called STATESOF_USA on the USA_STATES table by using the CREATE TRIGGER statement. Then we have used the AFTER trigger on the INSERT statement which means that it will execute the INSERT statement for the USA_STATES table.

And it will later execute the SQL STATEMENT which is used inside the CREATE TRIGGER statement. The INSERT statement is used to insert one new record into the USA_STATES table. Then the execution of the SELECT statement says that it will retrieve all records from the USA_STATES table 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 20 in the USA_STATES table.

If the WHERE condition gets a FALSE value then the SELECT statement will bring the empty record set for the output. The SELECT statement will retrieve all records from the USA_STATES table if the WHERE condition gets a TRUE value.

AFTER UPDATE TRIGGER EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER USA_STATES_TRIGGER
ON USA_STATES
AFTER UPDATE 
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>=20;

UPDATE USA_STATES
SET STATE_NAME='California'
WHERE STATE_ID=24;

• In this preceding query, a trigger is created called USA_STATES_TRIGGER by using the CREATE TRIGGER statement on the USA_STATES table.
• As the AFTER trigger is fired then it means the DML statement i.e.; (INSERT, DELETE and UPDATE) should be executed.
• In the UPDATE statement, it will update and set a new value of the STATE_NAME column as California 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 turns out to be TRUE then the SELECT statement will retrieve all records from the USA_STATES table.
• In the same table, we have inserted a new record and updated the same record by using the INSERT and UPDATE statements.

We hope that you have understood how to use the SQL Server AFTER trigger used with the INSERT and UPDATE statement on the same table. For a better explanation, we have used an example and explained it in depth.

Read: Alter view in SQL Server

SQL Server Trigger After Insert Update Timestamp

We will learn and understand how to use the SQL Server AFTER trigger with TIMESTAMP keyword on the INSERT and UPDATE statement on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_TIMESTAMP
ON USA_ALPHABET_COMPANY
AFTER INSERT, UPDATE
AS 
SELECT * FROM USA_ALPHABET_COMPANY
WHERE EMPLOYEE_ID >=5;

INSERT INTO USA_ALPHABET_COMPANY
VALUES(11,'John','Wick','2021-05-06');

UPDATE USA_ALPHABET_COMPANY
SET EMPLOYEE_FIRSTNAME='Jake'
WHERE EMPLOYEE_ID=8;

Here in the above query, we have created a trigger called AFTER_TIMESTAMP for the USA_ALPHABET_COMPANY table by using the CREATE TRIGGER statement.

Then we used the AFTER trigger on the INSERT and UPDATE statement which means once the AFTER trigger is fired then the DML statement which is used inside the query will be executed. And then the SQL STATEMENTS will be executed inside the AS clause.

The INSERT INTO statement will insert a new record in the USA_ALPHABET_COMPANY table.

In the UPDATE statement, it is used to update and set a new value of the EMPLOYEE_FIRSTNAME column as Jake in the USA_ALPHABET_COMPANY table with the WHERE condition.

In the WHERE condition, the EMPLOYEE_ID column is used with the EQUAL TO operator to find a value equal to 8 from the USA_ALPHABET_COMPANY table. The UPDATE statement will update and set a value of that column when the WHERE condition turns out to be TRUE.

In the AS clause, the SELECT statement will retrieve all records from the USA_ALPHABET_COMPANY table with the WHERE condition. In the WHERE condition, the EMPLOYEE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 5 from the USA_ALPHABET_COMPANY table.

The SELECT statement will retrieve all records from the USA_ALPHABET_COMPANY table if the WHERE condition turns out to be TRUE. If the WHERE gets a FALSE value then the SELECT statement will retrieve the empty record set for the output by the USA_ALPHABET_COMPANY table.

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

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

• How to call a view in SQL Server
• SQL Server Trigger Update
• Indexed views in SQL Server
• View SQL Server Error Logs
• Arithmetic operators in SQL Server

Consequently, in this post, we learned how to use the “SQL Server Trigger After Insert Update” statements. To assist you comprehend the idea better, we also discussed and learned about a variety of examples. The whole list of subjects we’ll discuss is provided below.

• SQL Server Trigger After Insert Update
• SQL Server Trigger After Insert Update Example
• SQL Server Trigger After Insert Update Delete
• SQL Server Trigger After Update Inserted Table
• SQL Server Trigger After Insert Update Another Table
• SQL Server Trigger After Insert Update Specific Column
• SQL Server Trigger After Insert Update Same Table
• SQL Server Trigger After Insert Update Timestamp