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.
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