Logical Tables in SQL Server: Inserted and Deleted Logical Table

Logical Tables in SQL Server: Inserted and Deleted Logical Table

30 Jul 2024
Intermediate
205K Views
6 min read
Learn via Video Course & by Doing Hands-on Labs

SQL Server Course

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

Read More - Top 50 DBMS Interview Questions and Answers

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 Classes Schedule

Our learn-by-building-project method enables you to build practical/coding experience that sticks. 95% of our learners say they have confidence and remember more when they learn by building real world projects.
ASP.NET Core Certification TrainingNov 08MON, TUE, WED, THU, FRI
Filling Fast
07:00AM to 08:30AM (IST)
Get Details
Azure Developer Certification TrainingNov 09SAT, SUN
Filling Fast
08:30PM to 10:30PM (IST)
Get Details
.NET Solution Architect Certification TrainingNov 09SAT, SUN
Filling Fast
05:30PM to 07:30PM (IST)
Get Details
.NET Microservices Certification TrainingNov 09SAT, SUN
Filling Fast
05:30PM to 07:30PM (IST)
Get Details
Microsoft Azure Cloud Architect with AINov 10SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details
ASP.NET Core Certification TrainingNov 10SAT, SUN
Filling Fast
10:00AM to 12:00PM (IST)
Get Details
Angular Certification TrainingNov 17SAT, SUN
Filling Fast
06:00PM to 08:00PM (IST)
Get Details
Software Architecture and Design TrainingNov 24SAT, SUN
Filling Fast
10:00AM to 12:00PM (IST)
Get Details
.NET Solution Architect Certification TrainingNov 24SAT, SUN
Filling Fast
10:00AM to 12:00PM (IST)
Get Details

Can't find convenient schedule? Let us know

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 9th time in a row (2016-2024). 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