Logical Tables in SQL Server: Inserted and Deleted Logical Table

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.

👉 Learn more about such concepts with our SQL Free Certification Course and enhance your database skills with hands-on examples.

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.

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

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 

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

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.

Read More - Top 50 SQL Interview Questions And Answers

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 and Microsoft SQL Server Training Online Free.

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.

• .NET Developer Training With Certification
• ASP.NET Core Certification Training
• ASP.NET Core Course
• .NET Solution Architect Certification Training
• Full-Stack .NET Developer Certification Training Program
• Advanced Full-Stack .NET Developer Certification Training

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