What are magic tables in SQL Server?

Introduction

Magic tables are the logical temporary tables created by SQL server internally to hold recently inserted values in the case of insert/update and to hold recently deleted values in the case of delete.

There are two types of Magic tables created at the time of insertion/updation/deletion in SQL server:

• INSERTED
• DELETED

You can catch/feel these magic tables in the trigger and also you can access it in trigger. These magic tables are usefull for roll back in one transaction and also usefull for find out the old and new values in the DML commands.

In DML (Data Manupulattion Language) commnand we have following magical table accordigly

• Insert: Whenever we perform insert operation in a SQL table, 'INSERTED' magic table is created
• Update: Whenever we perform update operation in a SQL table, 'INSERTED' and 'DELETED' magic tables created created
• Delete: Whenever we perform delete operation in a SQL table, 'DELETED' magic table is created

The 'INSERTED' magic table holds the new values of an insert/update operation.
The 'DELETED' magic table holds the old values of an update/delete operation.

Example

In this example we will see how we can use magic tables in trigger

Suppose, create a table say 'Emp' with some records

CREATE TABLE Emp(
    EmpId int NOT NULL,
    EmpName varchar(100) NULL,
    EmpSalary decimal(18,2) NULL
)

INSERT INTO Emp VALUES (1, 'ram',10000)
INSERT INTO Emp VALUES (2, 'Syam',11000)
INSERT INTO Emp VALUES (3, 'manish',15000)

SELECT * FROM Emp

Now create different triggers

Insert trigger

CREATE TRIGGER trg_Emp_ins
ON Emp
FOR INSERT
AS
BEGIN
    SELECT * FROM INSERTED -- show data in INSERTed logical table
    SELECT * FROM DELETED -- show data in Deleted logical table
END

Update trigger

CREATE TRIGGER trg_Emp_Upd
ON Emp
FOR UPDATE
AS
BEGIN
    SELECT * FROM INSERTED -- show data in INSERTED logical table
    SELECT * FROM DELETED -- show data in DELETED logical table
END

Delete trigger

CREATE TRIGGER trg_Emp_Del
ON Emp
FOR DELETE
AS
BEGIN
    SELECT * FROM INSERTED -- show data in INSERTED logical table
    SELECT * FROM DELETED -- show data in DELETED logical table
END

Now we will do different DML operation and we will see the output

Insert

INSERT INTO Emp VALUES (4, 'NICE',20000)

Output

Update

UPDATE Emp SET EmpName = 'NICE ONE' WHERE EmpId = 4

Output

Delete

DELETE FROM Emp WHERE EmpId = 4

Output

Conclusion

In the above blog we will understand the magic tables and their uses.