Magic Tables in SQL Server 2012

This article will give you an idea of how to use the magic table and also defines where you can use it.

SQL Server allows you to define a Magic Table. Magic Tables are invisible tables or virtual tables. You can see them only with the help Triggers in SQL Server. Magic Tables are those tables which allow you to hold inserted, deleted and updated values during insert, delete and update DML operations on a table in SQL Server. So let's have a look at a practical example of how to use Magic Tables in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
 

Magic Tables in SQL Server 

 
These are the two Magic Tables:
  1. Inserted
  2. Deleted
Generally Magic Tables are invisible tables, we can only see them with the help of Trigger's in SQL Server.
 
Use with Triggers
 
If you have implemented a trigger for a table then:
  1. Whenever you Insert a record on that table, that record will be shown in the INSERTED Magic Table.
  2. Whenever you Delete the record on that table, that record will be shown in the DELETED Magic Table Only. 
  3. Whenever you Update the record on that table, that existing record will be shown in the DELETED Magic Table and Updated new data will be shown in the INSERTED Magic Table.
Creating Table in SQL Server
 
Assume a table that looks as in the following figure.
 
Table-in-SQL-Server.jpg
 
Insert the Record in Table
 
Inserted Virtual Table
 
The Inserted table holds the recently inserted values. Hence that record will be shown in INSERTED Magic Table. Suppose we have UserDetails table as shown in following figure. Now We need to create a trigger to see data with in Inserted virtual tables. Now creating a trigger to see the data in Inserted virtual table.
  1. Create TRIGGER  Trigger_ForInsertmagic  
  2. ON [UserDetails]  
  3. FOR INSERT  
  4. AS  
  5. begin  
  6. SELECT * FROM INSERTED  
  7. end  
Now insert a new record in UserDetails table to see data with in Inserted virtual tables.
  1. insert into UserDetails values(12, 'Rahul''Sharma''Bombay')  
  2.   
  3. SELECT * FROM UserDetails  
Now press F5 to execute it.
 
Inserted-Magic-Table-in-SQL-Server.jpg
 
Delete the Record in Table
 
Deleted Virtual Table
 
The Deleted table holds the recently deleted values. Hence that record will be shown in the DELETED Magic Table. Suppose we have a UserDetails table as shown in the above figure. Now we need to create a trigger to see the data in the deleted virtual tables. To create a trigger to see the data in the deleted virtual table use the following:
  1. Create TRIGGER  Trigger_Fordeletemagic  
  2. ON [UserDetails]  
  3. FOR DELETE  
  4. AS  
  5. begin  
  6. SELECT * FROM Deleted  
  7. end  
Now delete a record in the UserDetails table to see the data in the Deleted virtual tables.
  1. Delete from UserDetails where User_id=12  
  2.   
  3. SELECT * FROM UserDetails  
Now press F5 to execute it.
 
Deleted-Magic-Table-in-SQL-Server.jpg
 
Update the Record in Table
 
To update the record in the UserDetails table, we use it for both virtual tables. One shows the inserted table and the other shows the deleted table. The following trigger defines boththe inserted table and the deleted table:
  1. Create TRIGGER  Trigger_ForInsertdeletemagic  
  2. ON [UserDetails]  
  3. FOR UPDATE  
  4. AS  
  5. begin  
  6. SELECT * FROM INSERTED  
  7. SELECT * FROM DELETED  
  8. end  
Now update the records in the UserDetails table to see the data in the inserted and deleted virtual tables.
  1. Delete from UserDetails where User_id=12  
  2.   
  3. SELECT * FROM UserDetails  
Now press F5 to execute it.
 
Update-with-Inserted-and-deleted-Magic-Table-in-SQL-Server.jpg