Magic Tables in SQL Server

Magic Tables are logical tables which are made consequently when DML activities (insert/update/delete”) are performed. These tables briefly hold the information relying on the activity and are available in triggers.

 

There are two sorts of magic tables.

  • Inserted
  • Deleted

What’s more, how does magic table work?

Magic Tables Inserted/Deleted are made relying upon the DML task “insert/update/delete” performed

Insert:

Inserted table is made when an insert task is performed and holds as of late inserted information in the table.

Update:

Both Inserted and Deleted tables are made when an update task is performed. Inserted table holds the updated lines (new information) while deleted table holds the old information of the columns which are updated because of update proclamation.

Delete:

Deleted table is made when delete task is performed and holds as of late deleted information from the table.

 

Example:-

  1. CREATETABLE EmpDetail
  2. (
  3. EmpId INT NOT NULL ,
  4. Emp_Name VARCHAR(15) NULL ,
  5. Empsalary INT NULL
  6. );
  7. INSERTINTO 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 table end
  • 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 as of late deleted or updated values, at the end of the day old information esteems. Subsequently the old updated and deleted records are inserted into the Deleted table.

Example:-

Create Deleted trigger

Coming up next is a case of making a 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”

Summary

I trust this article is useful to comprehend Magics Tables. I might want to have input from my blog perusers. Your significant criticism, question, or remarks about this article are constantly welcome.

Leave a Reply

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