What is Magic table in SQL ?

Magic tables are nothing but the logical tables maintained by SQL server internally.

There are two types of Magic tables available in SQL server:

• Inserted
• Deleted

We can not see or access these tables directly, not even their data-type. The only method to have access to these tables is Triggers operation either After Trigger or Instead of trigger.

 

Inserting into Table (Inserted Table):

 

Whenever we do  insert anything in our base table in database, a table gets created automatically by the 

SQL server, named as INSERTED. In this table current updated or inserted record will be available. we can access this table of record via triggers.

 

Updating Table (Inserted & Deleted Table):

 

Whenever we do any deletion operation on our base table, in spite of one, two tables are created, one is INSERTED and another is called DELETED. Deleted table consist of the current record after the deletion operation and  Inserted table consists of the previous record. We can access it via Triggers functionality. 

 

Deleting (Deleted Table):

 

Whenever we do deletion in base table in database, a table gets created automatically by the SQL server, named as

DELETED table. This table consist of current updated record after deletion operation. Again we can have access to these records via triggers.

inother words
In SQL server magic table is nothing more than an internal table which is created by the SQL server to recover recently inserted, deleted and updated data into SQL server database. That is when we insert or delete any record from any table in SQL server then recently inserted or deleted data from table also inserted into inserted magic table or deleted magic table with help of which we can recover data which is recently used to modify data into table either use in delete, insert or update to table. Basically there are two types of magic table in SQL server namely: inserted and deleted, update can be performed with help of these twos. Generally we cannot see these two table, we can only see it with the help Trigger's in SQL server. 

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