Magic Tables in SQL Server

Magic Tables in SQL Server

Magic Tables are logical tables which are created automatically when DML operations (insert/update/delete”) are performed. These tables temporarily hold the data depending upon the operation and are accessible in triggers.

There are two types of magic tables

·       Inserted

·       Deleted 

How does magic table work?

Magic Tables Inserted/Deleted are created depending on the DML operation “insert/update/delete”
performed.

 Insert: Inserted table is created when an insert operation is performed and holds recently inserted data in the table.

Update: Both Inserted and Deleted tables are created when an update operation is performed. Inserted table holds the updated rows (new data) while deleted table holds the old data of the rows which are updated as a result of update statement.

Delete: Deleted table is created when delete operation is performed and holds recently deleted data from the table.

Example:-

 

1. CREATE TABLE EmpDetail  

(  

2.    EmpId INT NOT NULL ,  

3.    Emp_Name VARCHAR(15) NULL ,  

4.     Empsalary INT NULL 

);   

7. INSERT  INTO EmpDetail  

8. VALUES ( 1, ‘ram’, 10000 ),  

9.        ( 2, ‘ramesh’, 11000 ) ,  

10.      ( 3, ‘manish’, 12000 );  

11. SELECT  * FROM    EmpDetail;  

OUTPUT :-



 

Inserted Magic table Inserted table holds the recently inserted values, in other words new data values. Hence recently added records are inserted into the Inserted table.

Example:-

Create Inserted trigger

The following is an example of creating an inserted trigger on a table: EmpDetail

  • CREATE TRIGGER trg_Emp_ins  
  • ON EmpDetail  
  • FOR INSERT  
  • AS  
  • begin  
  • SELECT * FROM INSERTED — show data in Inserted logical table  
  • SELECT * FROM DELETED — show data in Deleted logical tableend  
  • End  

Now insert records into the “EmpDetail” table to see the data within the Inserted and Deleted magic table.

  1. INSERT  INTO EmpDetail( EmpId, Emp_Name, Empsalary )  
  2. VALUES  ( 4, ‘Shivom’, 14000 );  
  3. SELECT  * FROM    EmpDetail;  

 OutPUT:-

Deleted Magic table

The Deleted table holds the recently deleted or updated values, in other words old data values. Hence the old updated and deleted records are inserted into the Deleted table.

Example:-

Create Deleted trigger

The following is an example of creating an deleted trigger on a table: EmpDetail

  1. CREATE TRIGGER trg_Emp_Upd ON EmpDetail  
  2.     FOR UPDATE  
  3. AS  
  4.     BEGIN  
  5.         SELECT  * FROM    INSERTED; — show data in INSERTED logical table  
  6.         SELECT  * FROM    DELETED; — show data in DELETED logical table  
  7.     END;  

 OUTPUT:-

Now update the record in the “EmpDetail”  

Leave a Reply

Your email address will not be published. Required fields are marked *