Magic Table for update operation in SQL Server 2008
When we perform an insert, delete or update operation on any table then the data is put into the temporary object created by server internally in the form of table called Magic Tables.
In my previous article I explained about Inserted Magic
Table and Deleted Magic Table. Now in this article I will
explain about Magic Tables for update operation.
Magic Table for update operation
Basically there are two
types of magic table in SQL server: inserted and deleted, update can be
performed with help of these two magic tables.
Magic tables can be used with Triggers and
Non-Triggers also i.e. Stored Procedures. When we perform an update operation
then the record will first deleted and maintains the row in Deleted Table and
updated data will be maintained in Inserted Table. Lets see the following
demonstration of magic table for update operation.
Create table and insert some values
A Statement that is used to fetch data from
* from empl
Creation of magic table for update operation
Demonstration of creating
trigger to by which we can see the inserted magic table and deleted magic table.
will first deleted and maintains the row in Deleted Table
* from deleted
data will be maintained in Inserted Table
* from inserted
Now update some value so
that Update_MagicTable trigger fires and inserted and deleted table table are
empl set ename='john'