Magic Tables in SQL Server

What are Magic Tables in SQL Server?

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.

 

Whenever a trigger fires in response to the INSERT,DELETE,or UPDATE statement,two special tables are created.These are the inserted and the deleted tables.They are also referred to as the magic tables.These are the conceptual tables and are similar in structure to the table on which trigger is defined(the trigger table).

While using triggers these Inserted & Deleted tables (called as magic tables) will be created automatically.

The inserted table contains a copy of all records that are inserted in the trigger table.

The deleted table contains all records that have been deleted from deleted from the trigger table.

Whenever any updation takes place,the trigger uses both the inserted and deleted tables.

When we insert any record then that record will be added into this Inserted table initially, similarly while updating a record a new entry will be inserted into Inserted table & old value will be inserted into Deleted table. In the case of deletion of a record then it will insert that record into the Deleted table.

Note that the Magic Table does not contain the information about the columns of the data-type text, ntext, or image. Attempting to access these columns will cause an error.Let’s see the demonstration of inserted magic table in SQL Server:

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.

Example:

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