The tables "INSERTED" and "DELETED" are called magic tables of the SQL Server. We can not see these tables in the database. But we can access these tables from the "TRIGGER" so inserted and deleted tables are called Magic Tables in the context of a trigger. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert, Update and Delete) on a database table.
1. When we insert the record into the table, the magic table "INSERTED" will be created In that table the current inserted row will be available. We can access this Record in the "TRIGGER".
2. When we update a record on the table where trigger is created, the magic tables "INSERTED" and "DELETED" both will be created, the Old data of the updating record will be available in "DELETED" table and, the new data will be available in "INSERTED" table, while accessing them inside the trigger.
3. When we delete the record from the table, the magic table "DELETED" will be created In that table the current deleted row will be available. We can access this Record in the "TRIGGER".
So 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 the 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
These two tables, inserted and deleted, are called magic tables.
Example of Magic tables :
Step 1: Create ‘tblCustomer’ table and insert some records
CREATE TABLE [dbo].[tblCustomer](
[empId] [int] NOT NULL,
[emp_Name] [varchar](15) NULL,
[emp_sal] [int] NULL
) ON [PRIMARY]
GO
insert into tblCustomer values (1, ‘Nitin’,10000)
insert into tblCustomer values (2,’ Rahul’,11000)
insert into tblCustomer values (3,’ prashant’,12000)
Step 2- Create a trigger on the tblCustomer table for the insetting new record
Step 3: Now insert one new record into the tblCustomer table to see the data within the Inserted and Deleted magic table.
Deleted magic table example :
The Deleted table holds the recently deleted or updated values.so the old updated and deleted records are inserted into the Deleted table.
