09
NovLogical 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.
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:
- Using Triggers
- 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.
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.
- .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.