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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s