Logical Tables in MS SQL

By AMIT S VAISHNAV
main Image
There are Inserted and Deleted logical tables in SQL Server. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.
Use of logical tables

Basically, logical tables are used by triggers for the following purpose:
1. To test data manipulation errors and take suitable actions based on the errors.
2. To find the difference between the state of a table before and after the data modification and take actions based on that difference.

Inserted logical Table

The Inserted table holds the recently inserted or updated values means new data values. Hence newly added and updated records are inserted into the Inserted table.

Suppose we have Employee table as below.

logical table 1

Now We need to create two triggers to see data with in logical tables Inserted and Deleted.

CREATE TRIGGER trg_Emp_Insert
ON Employee
FOR INSERT
AS
BEGIN
SELECT * FROM INSERTED -- show data in Inserted logical table
SELECT * FROM DELETED -- show data in Deleted logical table
END

When we execute this trigger we get the following output as below

logical table 2

Now insert a new record in Employee table to see data with in Inserted logical table and we also select the table data to see the changes.

INSERT INTO Employee(EmpID, Name, Salary) VALUES(3,'Avin',23000)
SELECT * FROM Employee

logical table 3

Deleted logical Table

The Deleted table holds the recently deleted or updated values means old data values. Hence old updated and deleted records are inserted into the Deleted table.

CREATE TRIGGER trg_Emp_Update
ON Employee
FOR UPDATE
AS
BEGIN
SELECT * FROM INSERTED -- show data in INSERTED logical table
SELECT * FROM DELETED -- show data in DELETED logical table
END

logical table 4

Now update the record in Employee table to see data with in Inserted and Deleted logical tables and we also select the table data to see the changes.

Update Employee set Salary=43000 where EmpID=3
SELECT * FROM Employee

logical table 5

We could not create the logical tables or modify the data with in the logical tables. Except triggers, When you use the OUTPUT clause in your query, logical tables are automatically created and managed by SQL Server. OUTPUT clause also has access to Inserted and Deleted logical tables just like triggers.

I hope this article will help you.