Magic Tables in SQL Server
In this article, I will discuss the Magic Tables in SQL Server with some examples. Please read our previous article before proceeding to this article where we discussed the basics of Triggers in SQL Server.
What are Magic Tables in SQL Server?
The Magic Tables in SQL Server are the specially created table which is available only inside of a trigger when we perform the insert, update, and delete operations.
So, we can think the magic tables are invisible tables or virtual tables and we can see and use them only with the help of Triggers in SQL Server.
Magic tables are those tables which are basically used to hold the INSERTED, DELETED and UPDATED values during insert, delete and update DML operations on a table in SQL Server.
There are two types of magic tables in SQL Server namely INSERTED and DELETED magic table.
UPDATE can be performed with the help of these two.
Let us understand the Magic Tables with some examples.
We are going to use the below Employee table to understand the Triggers in SQL server.
Please use below SQL Script to create and populate the Employee table with some test data.
-- Create Employee table CREATE TABLE Employee ( Id int Primary Key, Name nvarchar(30), Salary int, Gender nvarchar(10), DepartmentId int ) GO -- Insert data into Employee table INSERT INTO Employee VALUES (1,'Pranaya', 5000, 'Male', 3) INSERT INTO Employee VALUES (2,'Priyanka', 5400, 'Female', 2) INSERT INTO Employee VALUES (3,'Anurag', 6500, 'male', 1) INSERT INTO Employee VALUES (4,'sambit', 4700, 'Male', 2) INSERT INTO Employee VALUES (5,'Hina', 6600, 'Female', 3)
What is INSERTED Magic Table?
The Inserted Magic table is created when we perform an insert operation that provides the access to the values being inserted into the table. So, whenever we insert the values into a table those values we can see in the inserted magic table
Let us see an example for better understanding.
CREATE TRIGGER trInsertEmployee ON Employee FOR INSERT AS BEGIN SELECT * FROM INSERTED END
Let’s Insert one record
INSERT INTO Employee VALUES (6,’Saroj’, 7700, ‘Male’, 2)
So when we execute the above insert statement, then the data is inserted as expected in the Employee table along with it also display the data as shown below.
From the above output, it proofs that the INSERTED magic table will hold the inserted values. So, the INSERTED table is a special table used by DML triggers. When we add a new row into Employee table a copy of the row will also be made into the INSERTED table which only a trigger can access. We cannot access this table outside the context of the trigger. The structure of the inserted table will be identical to the structure of Employee table.
What is Deleted Magic Table?
The Deleted Magic table in SQL Server is created when we perform a delete operation providing access to the record being deleted. So, in simple word, we can say that, whenever we delete a Record from a table the deleted record information we can view with the help of deleted magic as part of a trigger.
Let us understand this with an example.
CREATE TRIGGER trDeleteEmployee ON Employee FOR DELETE AS BEGIN SELECT * FROM DELETED END
Let’s Delete one record from the Employee table
DELETE FROM Employee WHERE Id = 6
So when we execute the above Delete statement, the data gets deleted from the Employee table whose Id is 6 along with it also display the following deleted data.
So this proofs that the delete magic table holds the data that is deleted from the table. So, the DELETED table is a special table used by DML triggers. When we delete a row from the Employee table, a copy of the deleted row will be made available in DELETED table, which only a trigger can access. Just like the INSERTED table, the DELETED table cannot be accessed outside the context of the trigger and the structure of the DELETED table will be identical to the structure of the Employee table.
How to view the updating data in a table?
When we perform an update operation, then we will be having both the inserted and deleted magic tables where the inserted magic table will hold the new values being inserted and the deleted magic table will hold the old values of the table.
So in a simple word, we can say that, whenever we update a record in the table, then we can view the new data in the inserted magic table and the old data in the deleted magic table.
Let’s us understand this with an example.
-- Create Update Trigger CREATE TRIGGER trUpdateEmployee ON Employee FOR UPDATE AS BEGIN SELECT * FROM DELETED SELECT * FROM INSERTED END -- let's Update one record from the Employee table UPDATE Employee SET Name = 'Sharma', Salary = 8000 WHERE Id = 5
So when we execute the above update statement, it gives us the below output.
So this proofs that, whenever we perform an update operation then the Deleted Magic Table will hold the OLD Data whereas the Inserted Magic Table will hold the new Data.
What will happen if we update multiple records at a time?
As of now, the employee table holds the below data
Here we can see that the Employee table having three employees whose Gender is Male. Let’s update the Salary of All Gender Employees to 20000 as shown in the below SQL query.
UPDATE Employee SET Salary = 20000 WHERE Gender = 'Male'
So when we execute the above code, then it will give us the below output.
So all the old records are stored in the Deleted Table whereas all the new data are stored in the Inserted Magic table.
In the next article, I will discuss the Real-Time examples of using DML Triggers in SQL Server.
In this article, I try to explain the Magic Tables in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.