Magic tables or Logical Tables in SQL server and how to access Magic Tables?

Magic tables in SQL:

Magic tables are the logical tables in SQL server. There are two types of logical tables in SQL server:

• Inserted
• Deleted

 These tables are automatically created and managed by SQL Server internally. These tables hold the recently inserted, deleted and updated values during Insert, Update and Delete operations on a table. These tables are not visible and accessible directly. There are two methods to access these tables

•  Using Triggers operation either After Trigger or Instead of trigger.
• Without Triggers Using “OUTPUT” Clause 

Using Triggers:

Inserted Logical Table:

Inserted logical table holds the latest inserted value or updated value in the table.

Whenever we do insertion or updating the record in table in database, a table gets created automatically by the SQL server, named as INSERTED.

CREATE TABLE [dbo].[emp_details](             [empid] [numeric](18, 0) NOT NULL,             [empname] [varchar](100) NULL,             [salary] [numeric](18, 0) NULL,)

Now Create a trigger

CREATE TRIGGER emp _Insertion ON emp_details FOR INSERT AS begin SELECT * FROM INSERTED SELECT * FROM DELETED End

Now Inert data in above table:

INSERT INTO  emp_details (empid, empname, salary) VALUES (201, ’XYZ’ ,1000)

Deleted Logical Table:

When we update the record in table then  two tables are created, one is INSERTED and another is called DELETED. Deleted table will hold the previous record after the updations and  Inserted table consists of the updated record.

CREATE TRIGGER emp_update ON emp_details FOR update AS begin SELECT * FROM INSERTED SELECT * FROM DELETED End

Update Record

update emp_details set empname='ABC' where empid=201

Without Triggers Using Output Clause:

Output Clause  with Insert Command:

INSERT into emp_details  (  [EmpID],  EmpName, Salary  ) OUTPUT  Inserted.[EmpID], Inserted.EmpName, Inserted.Salary VALUES (208, 'Delton', 15000);

Result: