Magic Tables in SQL Server

Introduction

Magic tables are the logical temporary tables created by the SQL server internally to recover recently inserted, deleted, and updated data into the SQL server. They are created during DML trigger execution. If you want to know more about DML triggers, you may refer to my previous article on DML Triggers.

Three types of Magic tables are created at the time of insert/update/delete in the SQL server.

• INSERTED Magic tables
• DELETED Magic tables
• UPDATED Magic tables

Magic tables are stored in temp DB just as a temporary internal table, and we can see them with the help of triggers. We can retrieve the information or the impacted records using these Magic tables.

Let’s see how this works with the use of a trigger.

• When we perform the insert operation, the inserted magic table will have a recently inserted record showing on top of the table.
• When we perform the delete operation, the deleted magic table will have a recently deleted record showing on top of the table.
• When we perform the update operation, the inserted magic table will have a recently updated record showing on top of the table.

Let’s consider the below table to see how this work.

SELECT * FROM StudentsReport;

Inserted Magic Table

Let’s create a trigger on the StudentsReport table to see if the values are inserted on the StudentsReport table and see if a virtual table or temp table (Magic table) is created with recently inserted records.

CREATE TRIGGER  TR_StudentsReport_InsertedMagic ON StudentsReport
FOR INSERT
AS
BEGIN
    SELECT * FROM INSERTED
END

Now when we insert the records in the StudentsReport table, at the same time inserted magic table will be created along with recently inserted records.

Now execute the below queries together.

INSERT INTO StudentsReport VALUES (6, 'Aashi', 'English', 90);

SELECT * FROM StudentsReport;

In the above screenshot, we can see that while inserting a record in the StudentsReport table, it’s showing a recently Inserted record in the temp table, and that temp table is inserted magic table.

Deleted Magic Table

Now let’s create a trigger on the StudentsReport table to see if the values are deleted from the StudentsReport table and if the Magic table is created for recently deleted records.

CREATE TRIGGER  TR_StudentsReport_DeletedMagic ON StudentsReport
FOR DELETE
AS
BEGIN
    SELECT * FROM Deleted
END

Now when we delete the records in the StudentsReport table, at the same time deleted magic table will be created, and it will have recently deleted records.

Now execute the below query together.

DELETE FROM StudentsReport WHERE StudentId = 6;

SELECT * FROM StudentsReport;

In the above screenshot, we can see that while deleting a record from the StudentsReport table, it’s also showing a recently deleted record in the temp table, and that temp table is deleted magic table.

Updated Magic Table

Now, Let’s create a trigger on the StudentsReport table to see if the values are updated on the StudentsReport table and if the Magic table is created for recently updated records.

CREATE TRIGGER  TR_StudentsReport_UpdatedMagic ON StudentsReport
FOR UPDATE
AS
BEGIN
    SELECT * FROM INSERTED
END

Now when we update the records in the StudentsReport table, at the same time updated magic table will be created along with recently updated records.

Now execute the below query together.

UPDATE StudentsReport SET Marks = 90 WHERE StudentId = 3;

SELECT * FROM StudentsReport;

In the above screenshot, we can see that while updating the record in the StudentsReport table, it’s also showing a recently updated record in the temp table, and that temp table is an updated magic table.

Conclusion

Magic tables are one of the features of DML Trigger and can be useful when we want to know the list of impacted records during trigger execution. In this article, first, we have gone through the overview of Magic tables. We have covered the three types of magic tables in detail.

I hope you have liked the article. Please let me know your inputs/feedback in the comments section below.