Magic Table for Insert Operation in SQL Server 2008

In this article I describe about the inserted magic table.
  • 1956

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

  1. Inserted (contains value going to insert)
  2. 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:

table.jpg

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

on vendors

for insert

as

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')

Output:

 insertedMagicTable.jpg

Inserted Magic table is created.

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.