Magic Table in SQL Server 2012

Introduction

Today we are going to play around with the Magic Table in SQL Server. When we perform an insert or update operation on any table then the data is put into tables of inserted and deleted data. These inserted and updated tables are known as Magic Tables or Virtual Table.

I assume you have basic knowledge of Stored Procedures. For more help visit, Triggers in SQL Server.

First of all we create a table named emp

Creation of table:

create table emp(empId int, empName varchar(15))

Insertion of data:

insert into emp
select 1,'d'union all
select 2,'e'union all
select 3,'e'union all
select 4,'p'union all
select 5,'a'union all
select 6,'k'

Output:

select * from emp



Magic Table:

These are Virtual Tables named inserted and updated that are created by SQL Server when we perform an insert, delete or update operation. We can't see the Magic Table directly, We use a trigger to see the Magic Table.

Type of Magic Table:

1. Inserted
2. Deleted

Magic Table for insert operation:

When we insert data into a table then data is first inserted in the inserted table.

We can use the following trigger to see the inserted Magic Table.

create trigger majic_insert
on emp
for insert
as
select * from inserted

Output:

insert into emp values(11,'d')

When we execute that command, the trigger magic_insert fires and shows the table inserted.



Magic Table for delete operation:

When we delete data from any table then data is put into the deleted table.

By using following trigger we can see the deleted Magic Table.

create trigger magic_delete
on emp
for delete
as
select * from deleted

Output:

delete from emp where empId=11

When we execute that command the trigger magic_delete fires and shows the table deleted.



Magic Table for update operation:

When we perform an update operation then data from the first table is deleted first, then after that the new updated data is inserted.

By using following trigger we can see the inserted and deleted Magic Table.

create trigger magic_update
on emp
for update
as
select * from inserted
select * from deleted

Output:

update emp set empName='deepak' where empId=1

When we execute that command the trigger magic_update fires and shows the tables inserted and deleted.



Summary:

In this article I described Magic Table in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.