Magic Tables in SQL Server

Magic Tables

There are two Magic tables available in SQL Server named "Inserted" and "Deleted".
These are mantained by SQL server for Internal processing whenever an update, insert of delete occur on a table.
so These are not physical tables, only Internal tables

When ever insert statement is fired the "Inserted" table is populated with newly inserted Row and
when ever delete statement is fired the "Deleted" table is populated with the deleted row.
in same way when update statement is fired both "Inserted" and "Deleted" table used for records, the Original row before updation get store in "Deleted" table and new row (Updated) get store in "Inserted" table.

The term “magic table” is nowhere in the official product documentation. “Magic tables” is apparently .NETspeak for “inserted and deleted tables”

- SQL Server creates and manages them.
- They live in memory.
- These tables are temporary, and only accessible from the statement that created them.

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.

For PHP Bros Please refer : http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

Compiled By Rajesh Rolen