Magic Tables are tables automatically created tables in SQL Server used to internally store the inserted, updated values for DML operations such as (
SELECT, DELETE, INSERT, UPDATE, etc).
There are two magic tables in SQL Server : "inserted" and "deleted". These tables are called magic tables because they do not exist physically.
* "inserted" table stores the data of last inserted row or “insert operation”.
* "deleted" table stores the data of last deleted row or “delete operation”.
Note : An update operation is actually a delete operation + an insert operation i.e., old data is deleted and new data is inserted.
For the sake of example, we assume there is a table with name Employee and columns ‘ID’ and ‘Name’.
SQL Server provides two ways to view data in magic tables.
Use Triggers : One way is to use triggers on delete, insert or update operations in a table to view the data in “inserted” and “deleted” magic tables.
CREATE TRIGGER Inserted_Trigger
ON Employee
FOR INSERT
AS
BEGIN
SELECT * FROM Inserted
END
Whenever an insert operation will be done on Employee table, this trigger will be called and this will show the “inserted” table.
Use OUTPUT clause : Another way is to use the OUTPUT clause to view data in “inserted” or “deleted” magic tables. OUTPUT returns the copy of data inserted into or deleted from a table.
INSERT INTO Employee OUTPUT Inserted.* VALUES('Narayan')
