Logical Tables in SQL Server: Inserted and Deleted Logical Table

30 Jul 2024

Intermediate

205K Views

6 min read

Logical Tables in SQL Server: An Overview

Logical Tables in SQL Server are also known as Magic tables or Virtual tables. 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.

How to Access Logical Tables in SQL Server?

Logical tables are not visible and accessible directly. There are two methods to access these tables:

Using Triggers
Using “OUTPUT” Clause

Read More: Triggers In SQL Server

Types of Logical Tables in SQL Server

1. Inserted Logical Table

The Inserted table holds the recently inserted or updated values i.e. new data values. Whenever we insert or update the record in a table in the database, a table gets created automatically by the SQL Server, named INSERTED.

Suppose we have an Employee table as shown in Fig. Now, we need to create two triggers to see data within logical tables Inserted and Deleted.

Inserted logical Table


CREATE TRIGGER trg_Emp_Ins
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

Now insert a new record in the Employee table to see data within the Inserted logical table.


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

Output

Now insert a new record in Employee table to see data within the Inserted logical table.

2. Deleted Logical Table

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


CREATE TRIGGER trg_Emp_Upd
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

Deleted logical Table


 --Now update the record in Employee table to see data with in Inserted and Deleted logical tables
Update Employee set Salary=43000 where EmpID=3
SELECT * FROM Employee

Output

Deleted logical Table

We could not create the logical tables or modify the data within the logical tables. Except for 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.

Use of logical tables

Logical tables are used by triggers for the following purposes:

To test data manipulation errors and take suitable actions based on the errors.
To find the difference between the state of a table before and after the data modification and take actions based on that difference.

Summary

I hope you will enjoy these tips/tricks while programming with LINQ to SQL. If you want to gain a practical understanding, you can enroll in our SQL Server Course.

Do you Know?

.NET is gaining popularity day by day, especially after the release of .NET 8. .NET 8 is not only a framework version but much more than that. It redefines the way software applications are built and deployed, enabling developers to meet the evolving demands of modern computing.

Therefore, if you want to upskill yourselves and stand out from others consider our following training programs on .NET.