Inserted, Deleted Logical table(magic Table) in SQL Server
As per my studies, I found that there are two(2) Magic Tables(logical tables) -
1. Inserted
2. Deleted
What happens actually when you execute the update command or update the table?
when you execute the update command or update the table then it will first delete the record and then re-insert the new values or the reverse of that.
Magic Tables(logical tables) can be used with the following commands:
- Insert
- Delete
- Update
Note: to use Magic Tables you have to use OUTPUT in the query.
Magic tables are automatically managed and created by Microsoft SQL Server internally
to hold recently inserted, deleted and updated values during DML
operations (Insert,Update,Delete) on a database table.
These are not physical tables, only internal tables. Magic tables(Logical tables) is used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only but, In SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also.
Use of Magic Tables :
1. To test data manipulation errors and take suitable actions based on the errors.
2. To find the difference b/w the state of table before and after the data modification and take
actions based on the difference.
