Magic Table for Delete Operation in SQL Server 2008
In this article I explain about the magic table and how to make deleted magic table.
Introduction
Magic tables are nothing but inserted and deleted table. These are not physical tables, only virtual tables. 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. These are internal tables. Lets take a practical example to create magic tables. We can refer these tables in Triggers.
Types of Magic Tables
Magic tables are of two types
- Inserted (contains value going to insert )
- Deleted (contains value going to delete )
Example
First of all we create a table
create table vendors
(
vendorid int,
vendorname varchar(15),
vendorcity varchar(15),
vendorstate varchar(15)
)
Insert some values in vendors table
insert into vendors values (20,'vipendra','noida','up')
insert into vendors values (21,'deepak','lucknow','up')
insert into vendors values (22,'rahul','kanpur','up')
insert into vendors values (23,'malay','delhi','delhi')
insert into vendors values (24,'mayank','noida','up')
A Statement that is used to fetch data from vendors table
select * from vendors
Output:
Creation of magic table for delete operation
When we delete some value in vendors table, firstly deleted table will hold that data. Create trigger to refer deleted magic table
create trigger Deleted_MagicTable
on vendors
for delete
as
select * from deleted
Now delete value from table and see how trigger fires and deleted magic table holds that value.
delete vendors where vendorid= 22
Output:
Deleted Magic Table