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.
Introduction
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.
Example
Create table and insert some values
create table empl
(
eid int,
ename varchar(20)
)
insert into empl
select 101,'steve' union all
select 102,'joe' union all
select 103,'harry'
A Statement that is used to fetch data from empl table
select * from empl
Output:
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
on empl
for update
as
--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
Output:
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