//
you're reading...
SQL Server

What are magic table in Sql server?

Download/Read full PDF version of article here

1) Magic tables are nothing but inserted and deleted which are temporary object created by server internally to hold the recently inserted values in the case of insert and to hold recently deleted values in the case of delete, to hold before updating values or after updating values in the case of update.

Let us suppose if we write a trigger on the table on insert or delete or update. So on insertion of record into that table, inserted table will create automatically by database, on deletion of record from that table; deleted table will create automatically by database,

2) This two tables inserted and deleted are called magic tables.

3) Magic tables are used to put all the deleted and updated rows. We can retrieve the column values from the deleted rows using the keyword “deleted”

4) These are not physical tables, only internal tables.

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

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

7) Using with Triggers:
If you have implemented any trigger for any Tables then,
A.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table.
B. 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.
C. Whenever you delete the record on that table, that record will be there on DELETED Magic table only.
These magic tables are used inside the Triggers for tracking the data transaction.

8.) 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.

Hope you enjoyed reading.

Cheers

Download/Read full PDF version of article here

About these ads

About iDunno

Vishal is a seasoned professional with hand on Experience on Mircrosoft Technologies. He always look for challenging IT position that allows him to learn new Microsoft Technologies while utilizing experience of Project Development and Software Engineering Ethics. A MCP in WCF ,and looking forward for more.

Discussion

7 thoughts on “What are magic table in Sql server?

  1. bhai tere dhabe main kya kya milta hai

    Posted by manish | 2011/04/16, 6:13 PM
  2. This article gave me an insight into Magic Table in SQL server from scratch.Its Simple to Understand and complete.I Enjoyed learning and I Thank You very much. I had found also another nice post over internet which nicely explain about magic table in sql server, The post link is…..

    http://mindstick.com/Blog/211/Magic%20Table%20in%20SQL%20Server

    Thanks to all for save my time.

    Posted by Pankaj Singh | 2011/11/14, 7:27 PM
  3. good one. It’s really help me to understand Magic Table concept.
    Thanks,

    Posted by Gaurav Dutt | 2012/04/11, 10:30 PM
  4. thank u, its very helpfull content

    Posted by Raj Dhakad | 2012/04/15, 1:51 PM
  5. I was suggested this blog through my cousin. I am no longer positive whether or not this put up is written by way of him as nobody else recognise such targeted about my problem. You’re wonderful! Thanks!

    Posted by Site to make money online | 2012/09/06, 1:38 AM
  6. I have been surfing online more than three hours today, yet I never found any interesting article like yours.

    It’s pretty worth enough for me. In my view, if all site owners and bloggers made good content as you did, the web will be a lot more useful than ever before.

    Posted by Monte | 2013/05/13, 6:52 PM
  7. Is magic table created by default when we perform normal insertion &Deletion?

    Posted by Ramesh | 2014/09/08, 11:03 PM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow Vishalnayan on WordPress.com

Blog Stats

  • 160,116 hits
Follow

Get every new post delivered to your Inbox.

Join 32 other followers

%d bloggers like this: