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')