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

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:

create table in sql.jpg

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

MagicTableforUpdateOperation.jpg

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.