Magic Tables in SQL Server

Magic tables are the temporary logical tables that are created by the SQL server whenever there are insertion or deletion or update( D.M.L)  operations. The recently performed operation on the rows gets stored in magic tables automatically. These are not physical table but they are just temporary internal tables. These magic tables can’t be retrieved directly, we need to use triggers to access these magic tables to get the deleted and inserted rows.

When the following operations are done :
 

• INSERT – 
  The recently inserted row gets added to the INSERTED magic table.
   
• DELETE –
  The recently deleted row gets added to the DELETED magic table.
   
• UPDATE –
  The updated row gets stored in INSERTED magic table and the old row or previous row gets stored in the DELETED magic table.

Let us see how this works by using MSSQL as a server:

Creating a database :
Creating a database GeeksForGeeks by using the following SQL query as follows.

CREATE DATABASE GeeksForGeeks;

Using the database :
Using the database student using the following SQL query as follows.

USE GeeksForGeeks;

Creating table students with SQL query as follows:

CREATE TABLE students
( 
 stu_id varchar(10),
 stu_name varchar(20),
 branch varchar(20)
);

Verifying the database :
To view the description of the table in the database GeeksForGeeks using the following SQL query as follows.

EXEC sp_columns students;

Inserting data into the table :
Inserting rows into students table using the following SQL query as follows:

INSERT INTO students VALUES
('1901401','DEVA','C.S'),
('1901402','HARSH','C.S'),
('1901403','ABHISHEK','C.S'),
('1901404','GARVIT','C.S'),
('1901405','SAMPATH','C.S');

Verifying the inserted data :
Viewing the table after inserting rows by using the following SQL query as follows.

SELECT * FROM students;

Creating a trigger T1 on insert operation :

CREATE TRIGGER T1 ON students
AFTER INSERT
AS
BEGIN
SELECT * FROM INSERTED
END

Inserting entries to check how trigger retrieves INSERTED magic table :

INSERT INTO students VALUES
('1901406','PRADEEP','C.S'),
('1901407','DEVESH','C.S');
SELECT* FROM students ;

Creating a trigger T2 on delete operation :

CREATE TRIGGER T2 ON students
AFTER DELETE
AS
BEGIN
SELECT * FROM DELETED
END

Deleting entry to check how trigger retrieves DELETED magic table :

DELETE FROM students
WHERE stu_name = 'PRADEEP';
SELECT* FROM students ;

Creating a trigger T3 on update operation :

CREATE TRIGGER T3 ON students
AFTER UPDATE
AS
BEGIN
SELECT * FROM DELETED
SELECT* FROM INSERTED 
END

Updating entry to check how trigger retrieves DELETED, INSERTED magic tables since we find an old entry in a DELETED and updated entry in the INSERTED magic table :

UPDATE students SET stu_name= 'DEVANSH' 
WHERE stu_id = '1901401'
SELECT* FROM students