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
create table empl
insert into empl
select 101,'steve' union all
select 102,'joe' union all
A Statement that is used to fetch data from empl table
select * 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.
create trigger Update_MagicTable
--record will first deleted and maintains the row in Deleted Table
select * from deleted
-- updated data will be maintained in Inserted Table
select * from inserted
Now update some value so that Update_MagicTable trigger fires and inserted and deleted table table are displayed.
update empl set ename='john' where eid=101