What are Magic tables in SQL Server?

Question

What are temp tables? What is the difference between global and local temp tables?

Answer 1

Magic Tables

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.

Answer 2

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,
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 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