Magic Table

Magic Tables are invisible tables or virtual tables. You can see them only with the help Triggers in SQL Server. Magic Tables are those tables which allow you to hold inserted, deleted and updated values 

Magic tables are nothing but inserted and deleted which are temporary objects 
created by the server internally to hold recently inserted values in the case of insert and to hold recently deleted values in the case of delete, to hold before updating values or after updating values in the case of update.

USE WITH TRIGGERS

Use with Triggers

If you have implemented a trigger for a table then:

1. Whenever you Insert a record on that table, that record will be shown in the INSERTED Magic Table.
2. Whenever you Delete the record on that table, that record will be shown in the DELETED Magic Table Only. 
3. Whenever you Update the record on that table, that existing record will be shown in the DELETED Magic Table and Updated new data will be shown in the INSERTED Magic Table.

These are the two Magic Tables:

• Inserted
• Deleted

CREATE TABLE USERDETAILS

(

ID INT  NOT NULL,

NAME NVARCHAR(50)

)

Inserted Virtual Table

ALTER TRIGGER TRIGERUSERINSERT

ON USERDETAILS

FOR INSERT 

AS

BEGIN 

SELECT *   FROM INSERTED

END

INSERT INTO USERDETAILS VALUES(1,'DELEP KUMAR')

This query return current insert row

Deleted Virtual Table

CREATE  TRIGGER FORDELETE

ON USERDETAILS

FOR DELETE AS

BEGIN

SELECT * FROM DELETED

END

Delete from UserDetails where id=3

This query return current Deleted row

Update the Record in Table

Create TRIGGER  Trigger_ForInsertdeletemagic

ON [UserDetails]

FOR UPDATE

AS

begin

SELECT * FROM INSERTED

SELECT * FROM DELETED

end

update  UserDetails set Name='sonu' where id=12