Magic tables in sql server

The tables "INSERTED" and "DELETED" are called magic tables of the

SQL Server. We can not see these tables in the data base. But we can access these tables from the "TRIGGER".

Magic tables are used In SQL Server 6.5, 7.0 & 2000 versions with database Triggers only.

But, In SQL Server 2005, 2008 & 2008 R2 Versions magic tables can be use with Triggers and Non-Triggers also.

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.

Types of Magic tables:-

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.

Using with Triggers:

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

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 in the Deleted table.

These magic tables are used inside the Triggers for tracking the data transaction.

Using with Non-Triggers:

You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.

Following code defines  the magic table "INSERTED"

CREATE TRIGGER CreateMessage
ON EMPLOYEE
FOR INSERT
AS
   DECLARE @EMP_NAME varchar(50)
   SELECT @EMP_NAME= (SELECT EMP_NAME FROM INSERTED)
   INSERT INTO LOGTABLE(UserId,Message) values (@EMP_NAME,'Record Added')
GO

Following code Explain the magic table "DELETED"

CREATE TRIGGER CreateMessage
ON EMPLOYEE
FOR DELETE
AS
   DECLARE @EMP_NAME varchar(50)
   SELECT @EMP_NAME= (SELECT EMP_NAME FROM DELETED)
   INSERT INTO LOGTABLE(UserId,Message) values (@EMP_NAME,'Record Removed')
GO

Thank you guys... keep visiting …. :)

If you like this post then please join this site and like it’s Facebook page for getting updates and contributing into the problem solving of members of this blog. You can also post your issues on this blog's Facebook page and can get the solutions.