Magic Table in SQL Server

SQL Server allows us to define a Magic Table. Magic tables are nothing but invisible tables or virtual tables in SQL Server. We can see only with the help of 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.

Types of Magic Tables

Basically, there are two types of Magic tables in SQL Server namely

  • Inserted Magic Table
  • Deleted Magic Table

Update can be performed with the help of inserted and deleted magic tables.

Inserted Magic Table

When we insert the values into a table, those inserted values are holding by Inserted Magic Table in Buffer memory. Buffer memory is nothing but temporary memory in SQL Server.

Now, let us take an example to make clear, we will create an insert Trigger on EMP table.

EMP table:

Creating Trigger:

Checking:

Now, if we check inserted value from permanent table EMP, it has been inserted as shown below:

The relationship between actual table and Inserted Magic Table is shown below. this means that inserted magic table holds the values first in a Buffer memory and then transfers to the real table in SQL Server.

Deleted Magic Table

When we delete the values from the table, those deleted values are holding by Deleted Magic Table in a Buffer memory. We will clarify this by creating following trigger in SQL Server.

Checking:

Updating Data in Table

Whenever we update the data in the SQL Server table new value are holding by Inserted Magic Table and old values holding by Deleted Magic Table in Buffer memory. In the update case both IMT and DMT are used to handle it.

Following query is going to do for us:

Now, if we insert any values, then the Inserted Magic table will hold the inserted value and Deleted Magic table will hold nothing because we did not delete record shown below:

On the other hand if we delete a record from the EMP table, then the Deleted Magic table will hold the deleted value while Inserted Magic table hold nothing because we did not insert a record as we see following figure:

In the case of update, Inserted Magic table will hold new values and Deleted Magic table will hold old values shown below:

You people need to know that whenever we want to update a record, the SQL Server will delete an old values in which Deleted Magic table hold and will insert new values in which Inserted Magic table hold. In anther words SQL Server swap old values by new values.

The following figure shows us the relationship between Magic Table and DML operations.

As we see Magic table is useful for recovery of database records if anything goes wrong to return back.

Follow us:

Leave a Reply

Your email address will not be published. Required fields are marked *