Logical Tables in SQL Server: Inserted and Deleted Logical Table

Logical Tables in SQL Server: Inserted and Deleted Logical Table

18 Mar 2024
Intermediate
204K 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.

Read More: SQL Server Interview Questions and Answers

How to Access Logical Tables in SQL Server?

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

  1. Using Triggers
  2. 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:

  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.
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.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE

Share Article
Live Training Batches Schedule
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this