Magic Table for Insert Operation in SQL Server 2008
In this article I describe about the inserted magic table.
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)
First of all we create a table
create table vendors
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
Creation of magic tables for insert operation
When we insert some value in vendors table, firstly it will be inserted into the magic table. Create trigger to refer inserted magic table
create trigger Inserted_MagicTable
select * from inserted
Now insert value in table and see how trigger fires and value is saved in inserted magic table.
insert into vendors values (25,'nitin','noida','up')
Inserted Magic table is created.