What is magic table in sql server?
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"
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
If you have implemented any trigger for any Tables
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.