3
Answers

Magic Tables in SQL Server

Rohatash Kumar

Rohatash Kumar

12y
18.6k
1
Hi all,

What is magic table in sql server and In which situation we use it ?



Answers (3)
0
V S N Raju Kanumuri

V S N Raju Kanumuri

NA 456 6.5k 12y
Magic tables are nothing but INSERTED, DELETED table scope level, These are not physical tables, only Internal tables. 

This Magic table are used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only. 

But, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also. 

Using with Triggers: 
If you have implemented any trigger for any Tables then, 
1.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table. 
2.Whenever you Update the record on that table, That existing record will be there on DELETED Magic table and modified New data with be there in INSERTED Magic table. 
3.Whenever you Delete the record on that table, That record will be there on DELETED Magic table Only. 

These magic table are used inside the Triggers for tracking the data transaction. 

Using Non-Triggers: 
You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions. 
Accepted
1
Satyapriya Nayak

Satyapriya Nayak

NA 53k 8m 12y
Hi Rohatash,

Please refer the below links

http://vishalnayan.wordpress.com/2011/04/14/what-is-magic-table-in-sql-server/

http://www.dotnetspider.com/resources/29332-Magic-tables-SQL-Server.aspx

Thanks
1
Pradip Pandey

Pradip Pandey

NA 6.9k 1.1m 12y
Hi Rohtash
When you create a trigger either for insert or delete, 2 tables are created by SQL server & they are inserted & deleted table. These tables are called Magic Table.

Hope it will help.