Wednesday, May 20, 2009

Majic Tables in SQL Server

What are Majic Tables in SQL Server?





Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the inserted and the deleted tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).


While using triggers these Inserted & Deleted tables (called as magic tables) will be created automatically.

The inserted table contains a copy of all records that are inserted in the trigger table.

The deleted table contains all records that have been deleted from deleted from the trigger table.

Whenever any updation takes place,the trigger uses both the inserted and deleted tables.



When we insert any record then that record will be added into this Inserted table initially, similarly while updating a record a new entry will be inserted into Inserted table & old value will be inserted into Deleted table. In the case of deletion of a record then it will insert that record into the Deleted table.



Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.


No comments: