What are magic table in SQL Server?

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.

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"

This Magic table is 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, 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.

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.

Source: Stackoverflow.com