Magic tables :-

1. Magic tables are nothing but inserted and deleted which are temporary objects created by the server internally to hold recently inserted values in the case of insert and to hold recently deleted values in the case of delete, to hold before updating values or after updating values in the case of update.

Let us suppose if we write a trigger on the table on insert or delete or update. So on insertion of a record into that table, the inserted table will be created automatically by the database, on deletion of record from that table; the deleted table will be created automatically by the database.

2.These two tables, inserted and deleted, are called magic tables.

3.Magic tables are used to put all the deleted and updated rows. We can retrieve the column values from the deleted rows using the keyword "deleted".

4.These are not physical tables, only internal tables.

5.This Magic table is used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only.

6.But, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also.7.

7.Using with Triggers:

If you have implemented any trigger for any Tables then,

1. Whenever you insert a record into a table, that record will be in the INSERTED Magic table.
2. Whenever you update the record in that table, that existing record will be in the DELETED Magic table and the modified new data with be in the INSERTED Magic table.
3. Whenever you delete a record in that table, that record will be in the DELETED Magic table only.These magic tables are used inside the Triggers for tracking the data transaction.

8.Using Non-Triggers:

Use of magic tables

Basically, magic tables are used by triggers for the following purposes.

The following is an example of creating a table:

CREATE TABLE emp( empIdintNOTNULL,emp_Namevarchar(15)NULL, emp_salintNULL)

insert into emp values(1,'ram',10000)
insert into emp values(2,'ramesh',11000)
insert into emp values(3,'manish',12000)

SELECT * FROM emp

Inserted magic table

The Inserted table holds the recently inserted values, in other words new data values. Hence recently added records are inserted into the Inserted table.

Create inserted trigger

The following is an example of creating an inserted trigger on a table:

CREATE TRIGGER trg_Emp_ins
ON emp
FOR INSERT
AS
begin
SELECT * FROM INSERTED-- show data in Inserted logical table
SELECT * FROM DELETED-- show data in Deleted logical tableend
End

Output

Now insert records into the Emp table to see the data within the Inserted and Deleted magic table.

insert into emp(empId,emp_Name,emp_sal) values(4,'sita',14000)
select * from emp

Output

Deleted magic table

The Deleted table holds the recently deleted or updated values, in other words old data values. Hence the old updated and deleted records are inserted into the Deleted table.

Create deleted trigger

The following is an example of creating a deleted trigger on a table:

CREATE TRIGGER trg_Emp_Upd
ON emp
FOR UPDATE
AS
begin
SELECT * FROM INSERTED-- show data in INSERTED logical table
SELECT * FROM DELETED-- show data in DELETED logical table
End

Output

Now update the record in the Emp table to see the data in the Inserted and Deleted magic table.

update emp setemp_sal=13000 where empId=4
select * from emp

Output