What is Magic Table in SQL Server?

Introduction

Here, I will explain what is Magic Table and how to get this in SQL Server.

Magic Table

The tables “Inserted” and “Deleted” are called magic tables of the SQL Server. We cannot see these tables in the database, but we can access these tables from the “Trigger”. 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.

Use of Logical Tables

Generally, logical tables are used by triggers for the following purposes:

1. To identify data, which is error free?
2. To check the effect of data on the table

Inserted Logic Table

When we insert the record into the table, the magic table “Inserted” will be created and that table holds the current inserted row. We can access their record using “Trigger”.

Create LogMessage Table 

Create table LogMessage (Empid varchar(4),EmpName varchar(50),Message varchar(50))

Create Emp Table 

Create table Emp (Empid varchar(4),EmpName varchar(50)) 

Create Trigger For Display Current Inserted Row

Create Trigger LogDisplay
On Emp
for Insert
As
Select * from Inserted
Select * from Deleted
Go 

Create Trigger

Create Trigger LogInserted
On Emp
for Insert
As
Declare @EmpId varchar(4)
Declare @EmpName varchar(50)
Select @EmpId=(Select EmpId from Inserted)--get data for Inserted Logic Table
Select @EmpName=(Select Empname from Inserted) )--get data for Inserted Logic Table
Insert into LogTable (EmpId,EmpName,Message) values(@EmpId,@EmpName,'Record Added') --Save Current row in LogTable
Go 

Insert a Row in Emp Table

Insert into Emp (EmpId,EmpName) values('S001','Ravindra')

Here Two Triggers are Fired

1. LogDisplay
2. LogInserted

Deleted Logic Table

When we delete the record from the table, the magic table “Deleted” will be created and hold the current deleted row. We can also access these rows from “trigger”.

Create Trigger for Display Current Deleted Row

Create Trigger LogDisplayDel
On Emp
for Delete
As
Select * from Inserted
Select * from Deleted
Go 

Create Trigger

reate Trigger LogDelete
On Emp
for Delete
As
Declare @EmpId varchar(4)
Declare @EmpName varchar(50)
Select @EmpId=(Select EmpId from Deleted)
Select @EmpName=(Select Empname from Deleted)
Insert into LogTable (EmpId,EmpName,Message) values(@EmpId,@EmpName,'Record Deleted')
Go 

Delete a Row in Emp Table

Delete from Emp where Empid='s001' 

We could not create the logical tables or modify the data within the logical tables.

Traction History

select * from logTable 

Note: If you are use Truncate command, then no Trigger fired.

truncate table Emp