Magic Tables in SQL Server

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.

Magic Tables 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.Magic Tables in SQL Server

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.

Magic Tables in SQL Server

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.

Magic Tables in SQL Server

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.

Magic Tables in SQL Server

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

Magic Tables in SQL Server

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.

Magic Tables in SQL Server

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.

SUMMARY

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.

Leave a Reply

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