Magic Tables are logical tables which are made consequently when DML activities (insert/update/delete”) are performed. These tables briefly hold the information relying on the activity and are available in triggers.
There are two sorts of magic tables.
What’s more, how does magic table work?
Magic Tables Inserted/Deleted are made relying upon the DML task “insert/update/delete” performed
Inserted table is made when an insert task is performed and holds as of late inserted information in the table.
Both Inserted and Deleted tables are made when an update task is performed. Inserted table holds the updated lines (new information) while deleted table holds the old information of the columns which are updated because of update proclamation.
Deleted table is made when delete task is performed and holds as of late deleted information from the table.
- CREATETABLE EmpDetail
- EmpId INT NOT NULL ,
- Emp_Name VARCHAR(15) NULL ,
- Empsalary INT NULL
- INSERTINTO EmpDetail
- VALUES(1, ‘ram’, 10000 ),
- (2, ‘ramesh’, 11000 ) ,
- ( 3, ‘manish’, 12000 );
11.SELECT * FROM EmpDetail;
Inserted Magic table
Inserted table holds the recently inserted values, in other words new data values. Hence recently added records are inserted into the Inserted table.
Create Inserted trigger
The following is an example of creating an inserted trigger on a table: EmpDetail
- CREATE TRIGGER trg_Emp_ins
- ON EmpDetail
- FOR INSERT
- SELECT * FROM INSERTED — show data in Inserted logical table
- SELECT * FROM DELETED — show data in Deleted logical table end
Now insert records into the “EmpDetail” table to see the data within the Inserted and Deleted magic table.
- INSERT INTO EmpDetail( EmpId, Emp_Name, Empsalary )
- VALUES ( 4, ‘Shivom’, 14000 );
- SELECT * FROM EmpDetail;
Deleted Magic table
The Deleted table holds the as of late deleted or updated values, at the end of the day old information esteems. Subsequently the old updated and deleted records are inserted into the Deleted table.
Create Deleted trigger
Coming up next is a case of making a deleted trigger on a table: EmpDetail
- CREATE TRIGGER trg_Emp_Upd ON EmpDetail
- FOR UPDATE
- SELECT * FROM INSERTED; — show data in INSERTED logical table
- SELECT * FROM DELETED; — show data in DELETED logical table
Now update the record in the “EmpDetail”
I trust this article is useful to comprehend Magics Tables. I might want to have input from my blog perusers. Your significant criticism, question, or remarks about this article are constantly welcome.