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