Magic Tables in SQL Server 2012

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.

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.

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.

Create TRIGGER  Trigger_ForInsertmagic

ON [UserDetails]

FOR INSERT

AS

begin

SELECT * FROM INSERTED

end

Now insert a new record in UserDetails table to see data with in 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.