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-tabhle-in-sql-server-.jpg

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-in-sql-serverr.jpg

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-in-sql-serverr.jpg

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.

magic-table-in-sql-server-2012.jpg

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.

Up Next
    Ebook Download
    View all
    Learn
    View all