Magic Table in SQL Server

Magic Table

In SQL server magic table is nothing more than an internal table which is created by the SQL server to recover recently inserted, deleted and updated data into SQL server database. That is when we insert or delete any record from any table in SQL server then recently inserted or deleted data from table also inserted into inserted magic table or deleted magic table with help of which we can recover data which is recently used to modify data into table either use in delete, insert or update to table. Basically there are two types of magic table in SQL server namely: inserted and deleted, update can be performed with help of these twos. Generally we cannot see these two table, we can only see it with the help Trigger’s in SQL server. Let’s see the following

Example

INSERTED Magic Table

When we insert record into table then SQL server automatically created ‘inserted’ magic table and recently inserted record are available in this table, If we want to recover this data which are recently inserted then we can access this record with the help of Trigger’s. Let’s see the demonstration of inserted magic table in SQL Server.

CREATE TABLE TO DEMONSTRATION OF INSERT MAGIC TABLE 

CREATE TABLE INSERT_MAGIC(ID INT,TRIGGER_MESSAGE VARCHAR(50) )

 DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

  CREATE TRIGGER TRI_MAGIC_INSERT

  ON USERLOGIN

  INSTEAD OF INSERT

  AS  BEGIN

  DECLARE @ID INT

  SELECT @ID = (SELECT ID FROM inserted)

  INSERT INTO INSERT_MAGIC VALUES (@ID,'RECORD ADDED')

  END

  GO

Note: Here ‘inserted’ is insert magic table

DELETED Magic Table

When we delete record from the table then SQL automatically create a deleted magic table which holds are deleted record from original table if we want to recover it then we can access that data from deleted magic table. Let’s see demonstration of recover data from deleted magic table.

Example: Creating Trigger for Deleted Magic table

DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

  CREATE TRIGGER TRI_MAGIC_DELETE

  ON USERLOGIN

  INSTEAD OF INSERT

  AS  BEGIN

  DECLARE @ID INT

  DECLARE @NAME VARCHAR(50)

  SELECT @ID = (SELECT ID FROM deleted)

  SELECT @NAME = (SELECT NAME FROM deleted)

  INSERT INTO INSERT_MAGIC VALUES (@ID,@NAME)

  END

  GO