Magic tables are nothing but the logical tables or temporary objects maintained by SQL server internally.
- Holds recently inserted values in the case of insert.
- Holds recently deleted values in the case of delete.
- Holds before updating values or after updating values in the case of update.
- We cannot 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 or on DML operations.
- This Magic table is used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only.
- From SQL Server 2005, 2008 & 2008 R2 Versions we can use these Magic tables with Triggers and Non-Triggers also.
There are two types of Magic tables available in SQL server:
Using with Triggers
If there is any trigger on any Tables then,
- Whenever you insert a record into a table, that record will be in the INSERTED Magic table.
- 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.
- Whenever you delete a record in that table, that record will be in the DELETED Magic table only.
These magic tables are used inside the Triggers for tracking the data transaction.
Magic tables can also be used with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.