Magic Tables in SQL Server

Introduction

In this article, we will learn about Magic Tables in SQL Server. 

Magic Tables in SQL Server 

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 of Triggers in the SQL Server. Magic Tables are those tables that 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 using the SQL Server Management Studio.

Types of 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 Triggers 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.

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 a UserDetails table as shown in the following figure. Now We need to create a trigger to see data within Inserted virtual tables. Now creating a trigger to see the data in the Inserted virtual table.

Create TRIGGER  Trigger_ForInsertmagic  
ON [UserDetails]  
FOR INSERT  
AS  
begin  
SELECT * FROM INSERTED  
end  

Now insert a new record in the UserDetails table to see data within Inserted virtual tables.

insert into UserDetails values(12, 'Rahul', 'Sharma', 'Bombay')  
  
SELECT * FROM UserDetails  

Now press F5 to execute it.

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:

Create TRIGGER  Trigger_Fordeletemagic  
ON [UserDetails]  
FOR DELETE  
AS  
begin  
SELECT * FROM Deleted  
end  

Now delete a record in the UserDetails table to see the data in the Deleted virtual tables.

Delete from UserDetails where User_id=12  
  
SELECT * FROM UserDetails  

Now press F5 to execute it.

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 both the inserted table and the deleted table:

Create TRIGGER  Trigger_ForInsertdeletemagic  
ON [UserDetails]  
FOR UPDATE  
AS  
begin  
SELECT * FROM INSERTED  
SELECT * FROM DELETED  
end  

Now update the records in the UserDetails table to see the data in the inserted and deleted virtual tables.

Delete from UserDetails where User_id=12  
  
SELECT * FROM UserDetails  

Now press F5 to execute it.

Conclusion

In this article, we learned about Magic Tables in SQL Server.