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, 'Peter', 'English', 90);
SELECT * FROM StudentsReport;

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

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;

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.

HostForLIFE SQL Server 2019 Hosting
HostForLIFE is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.