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.
  • 2195

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 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:

deletedMagicTable.jpg

Deleted Magic Table

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.