Magic tables in SQL

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:

  1. Inserted
  2. Deleted

Using with Triggers

If there is any trigger on any Tables then,

  1. Whenever you insert a record into a table, that record will be in the INSERTED Magic table.
  2. 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.
  3. 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.

Using Non-Triggers

Magic tables can also be used with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s