What is Magic table in SQL ?

Posted by Nav234 under Sql Server on 7/8/2010 | Views : 57100 | Status : [Member] | Replies : 6
Hi all

This was an interview question .

What is Magic table in SQL ?

Thanks in advance

S.Naveen...


Responses

Posted by: PandianS on: 7/8/2010 [Member] [MVP] Silver

Up
0
Down
Hi

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.

Cheers








Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Titochhabra on: 11/14/2011 [Member] Starter | Points: 25

Up
0
Down
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.

For more Details please check out the following link...
http://mindstick.com/Blog/211/Magic%20Table%20in%20SQL%20Server

It might be helpful for you.


Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: perfectchourasia-9163 on: 11/14/2011 [Member] Starter | Points: 25

Up
0
Down
While using triggers these Inserted & Deleted tables
(called as magic tables) will be created automatically.

When we insert any record then that record will be added
into this Inserted table initially, similarly while
updating a record a new entry will be inserted into
Inserted table & old value will be inserted into Deleted
table.

In the case of deletion of a record then it will insert
that record in the Deleted table

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Aloksingh17.88 on: 11/29/2014 [Member] Starter | Points: 25

Up
0
Down
There are Inserted and Deleted logical tables in SQL Server. 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.

Logic:
1:Whenever you insert a record into a table, that record will be in the INSERTED Magic table.
2:Whenever you update the record in that table, that existing record will be in the DELETED Magic table and the modified new data with be in the INSERTED Magic table.
3:Whenever you delete a record in that table, that record will be in the DELETED Magic table only.

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Punitha on: 12/4/2014 [Member] Starter | Points: 25

Up
0
Down
What happens when you update the table or when you run the Update command?

Is it going to really update the existing record?

No, it will first delete the record and then re-insert the new changed values or the reverse of that.

Magic Tables can be used with the following commands:
1.Insert
2.Delete
3.Update

1. Insert
When we insert records into the table then the Inserted Table will be used and whatever we are inserting into the table is in the Inserted Table.

2. Delete
When we delete records the Deleted Table will be used, and the deleted records are stored in the Deleted Table.

3. Update
When we update records then both the Inserted and Deleted Tables are used; first it will delete the record and then insert and store these results in the Inserted & Deleted Tables





Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Amatya on: 5/16/2015 [Member] Silver | Points: 25

Up
0
Down
Inserted and Deleted are magic tables in SQL Server
Thanks

Feel free to share informations 9731764134
mail Id ' adityagupta200@gmail.com
Thanks

Nav234, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response