The Inserted Scan operator appears in an execution plan when working with triggers, specifically AFTER INSERT or INSTEAD OF INSERT triggers. This operator is used to scan the inserted pseudo-table, which is a temporary structure that SQL Server creates to hold the rows that are being inserted into a table within the context of a trigger. The Inserted Scan operator accesses these rows for any additional processing defined in the trigger.
Icon of Inserted scan
See the example
For the demo we are creating
employee table and audit table and creating trigger on the employee table.
|
-- Create Employees table CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY IDENTITY, FirstName NVARCHAR(50), LastName NVARCHAR(50), Position NVARCHAR(50), HireDate DATE ); -- Create EmployeeAudit table to store logs CREATE TABLE EmployeeAudit ( AuditID INT PRIMARY KEY IDENTITY, EmployeeID INT, FirstName NVARCHAR(50), LastName NVARCHAR(50), Position NVARCHAR(50), HireDate DATE, AuditDate DATETIME DEFAULT GETDATE() ); |
Now creating trigger on employee
table
|
-- Create AFTER INSERT trigger on Employees table CREATE TRIGGER trgAfterInsertEmployee ON Employees AFTER INSERT AS BEGIN -- Insert the new employee data into the audit table INSERT INTO EmployeeAudit (EmployeeID, FirstName, LastName, Position, HireDate) SELECT EmployeeID, FirstName, LastName, Position, HireDate FROM inserted; END; |
Now inserting a record into the
table.
|
INSERT INTO Employees (FirstName, LastName, Position, HireDate) VALUES ('Bagesh Kumar', 'Singh', 'Software Developer', GETDATE()); |
Run this script and see the
execution plan.
Read more about trigger: Triggerin SQL server
https://bageshkumarbagi-msbi.blogspot.com/2017/08/trigger-in-sql-server.html
