What are magic table in Sql server?

) Magic tables are nothing but inserted and deleted which are temporary object created by server internally to hold the 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 record into that table, inserted table will create automatically by database, on deletion of record from that table; deleted table will create automatically by database,
2) This 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) Using with Triggers:
If you have implemented any trigger for any Tables then,
A.Whenever you Insert a record on that table, That record will be there on INSERTED Magic table.
B. Whenever you update the record on that table, that existing record will be there on DELETED Magic table and modified new data with be there in INSERTED Magic table.
C. Whenever you delete the record on that table, that record will be there on DELETED Magic table only.
These magic tables are used inside the Triggers for tracking the data transaction.
8.) Using Non-Triggers:
You can also use the Magic tables with Non-Trigger activities using OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions.
Hope you enjoyed reading.
Cheers

There are Inserted and Deleted logical tables in SQL Server. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.

Use of logical tables

Basically, logical tables are used by triggers for the following purpose:

1. To test data manipulation errors and take suitable actions based on the errors.
2. To find the difference between the state of a table before and after the data modification and take actions based on that difference.

Inserted logical Table

The Inserted table holds the recently inserted or updated values means new data values. Hence newly added and updated records are inserted into the Inserted table.
Suppose we have Employee table as shown in fig. Now We need to create two triggers to see data with in logical tables Inserted and Deleted.

 CREATE TRIGGER trg_Emp_Ins
ON Employee
FOR INSERT
AS
begin
SELECT * FROM INSERTED -- show data in Inserted logical table
SELECT * FROM DELETED -- show data in Deleted logical table
end 

Now insert a new record in Employee table to see data with in Inserted logical table.

 INSERT INTO Employee(EmpID, Name, Salary) VALUES(3,'Avin',23000)
SELECT * FROM Employee 

Deleted logical Table

The Deleted table holds the recently deleted or updated values means old data values. Hence old updated and deleted records are inserted into the Deleted table.

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

 --Now update the record in Employee table to see data with in Inserted and Deleted logical tables
Update Employee set Salary=43000 where EmpID=3
SELECT * FROM Employee 

We could not create the logical tables or modify the data with in the logical tables. Except triggers, When you use the OUTPUT clause in your query, logical tables are automatically created and managed by SQL Server. OUTPUT clause also has access to Inserted and Deleted logical tables just like triggers.

Summary

I hope you will enjoy these tips/tricks while programming with LINQ to SQL. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.