How to get inserted value in trigger SQL Server

In this SQL Server tutorial, we will learn and comprehend How to get inserted value in trigger SQL Server 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.

• How to get inserted value in trigger SQL Server
• SQL Server Trigger After Insert get Inserted Value
• What is Inserted in SQL Server Trigger

Also, check the latest SQL Server tutorial: How to Debug an SQL Server Trigger

How to get inserted value in trigger SQL Server

In this SQL Server section, we will learn and understand how to get inserted value in SQL Server Trigger. And which will be explained with the help of an illustrated example.

in the INSERTED pseudo table, when we use the INSERT INTO statement to insert a new record into the table, it inserts one more record into the INSERTED pseudo table. Let’s see an example of using a trigger in the INSERTED pseudo table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_CANADA
ON CANADA_STATES
AFTER INSERT
AS 
SELECT * FROM INSERTED
WHERE STATE_ID >=14
ORDER BY STATE_ID DESC;

INSERT INTO CANADA_STATES
VALUES (28,'Angelina Jolie','Nunavut',5421);

In this preceding query, we have created a trigger called INSTEADOF_CANADA on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have used the AFTER trigger on the DML STATEMENT which means that once the AFTER trigger is fired then the DML STATEMENT will be triggered later the SQL STATMENT will be executed.

In the INSERT INTO statement, it will insert a new record into the CANADA_STATES table after the AFTER trigger is fired.

In the AS clause, the SELECT statement will retrieve all records from the INSERTED pseudo 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 14 and it will arrange the STATE_ID column in descending order by using the ORDER BY expression DESC.

So in the end, a new record will be inserted into the INSERTED pseudo table from the CANADA_STATES table.

We hope that you have understood the subtopic “How to get inserted value in trigger SQL Server” by using the CREATE TRIGGER statement, For a better understanding, we have used an example and explained it in depth.

Also, check: SQL Server Trigger Before Update

SQL Server Trigger After Insert get Inserted Value

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

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTERINSERT_USA
ON USA_STATES
AFTER INSERT
AS 
SELECT * FROM INSERTED
ORDER BY STATE_ID DESC;

INSERT INTO USA_STATES
VALUES (28,'North Dakota',56120,'Daymon Walker','Male');

In this preceding query, we have created a trigger called AFTERINSERT_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 once the AFTER trigger is fired then the DML STATEMENT will be triggered. After that, we will execute the SQL STATEMENT which is performed later.

In the INSERT INTO statement, we will insert a new record into the USA_STATES table once the AFTER trigger is fired.

In the AS clause, the SELECT statement will retrieve all records from the INSERTED pseudo table 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.

So once the order is arranged in the descending order, the SELECT statement will retrieve records from the USA_STATES table for the result set. This means that a new record will be inserted into the INSERTED pseudo table.

We hope that you have understood how to use the SQL Server INSERT INTO statement used with the AFTER trigger on the INSERTED pseudo-table by the query. For a better definition, we have used an example and clarified it in depth.

Read: SQL Server Trigger Before Insert

What is Inserted in SQL Server Trigger

Here we will learn and understand how to use the SQL Server AFTER trigger on the INSERTED pseudo table and will define the SQL Server INSERTED pseudo table. And which will be explained with the help of an illustrated example.

Following an INSERT or UPDATE command, copies of the new or modified rows are stored in the INSERTED table. The trigger table’s new or modified rows are copied to the inserted table during the execution of an INSERT or UPDATE statement.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_INSERTED
ON HARVARD_UNIVERSITY
AFTER INSERT
AS 
SELECT * FROM INSERTED
WHERE STUDENT_ID>=12;

INSERT INTO HARVARD_UNIVERSITY
VALUES (16,'Damon','Savlatore','savlatore.damon@tvd.com','Male','2021-05-06','2026-08-26');

In the aforementioned query, a trigger is created called AFTER_INSERTED on the HARVARD_UNIVERSITY table by using the CREATE TRIGGER statement. Then the AFTER trigger is used on the INSERT statement which means that once the trigger is fired the event will be executed.

In this query, once the AFTER trigger is fired then the DML STATEMENT will be executed after that the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be completed.

In the INSERT INTO statement, it is used to insert a new record into the HARVARD_UNIVERSITY table after the trigger is fired.

In the AS clause, the SELECT statement is used to retrieve all records from the INSERTED pseudo 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 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 turns out to be FALSE then the SELECT statement will retrieve all records from the INSERTED pseudo table.

This means that when a new record is inserted into the HARVARD_UNIVERSITY table then a new record will also be inserted into the INSERTED pseudo table.

We hope that you have understood the subtopic “What is Inserted in SQL Server Trigger” by using the AFTER trigger on the INSERTED pseudo table by the query. For a better exposition, we have used a model and described it in depth.

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

• Disable Trigger in SQL Server
• RIGHT JOIN in SQL Server
• How to execute Trigger in SQL Server
• Trigger For Delete SQL Server
• SQL Server Trigger On View
• SQL Server Trigger Update
• SQL Server String_agg

With this tutorial, we now know How to get inserted value in trigger SQL Server 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.

• How to get inserted value in trigger SQL Server
• SQL Server Trigger After Insert get Inserted Value
• What is Inserted in SQL Server Trigger