Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 29 November 2012

Magic table or Virtual table in SQL Server


Basically, magic table is the terminology used for virtual table in SQL Server since there is no magic in SQL Server. There are Inserted and Deleted virtual tables in SQL Server. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.

Use of virtual tables

Basically, virtual tables are used by triggers for the following purpose:
  1. To test data manipulation errors and take suitable actions based on the errors.
  2. To find the difference between the state of a table before and after the data modification and take actions based on that difference.

Inserted Virtual Table

The Inserted table holds the recently inserted or updated values means new data values. Hence newly added and updated records are inserted into the Inserted table.
Suppose we have Employee table as shown in fig. Now We need to create two triggers to see data with in virtual tables Inserted and Deleted.

  1. CREATE TRIGGER trg_Emp_Ins
  2. ON Employee
  3. FOR INSERT
  4. AS
  5. begin
  6. SELECT * FROM INSERTED -- show data in Inserted virtual table
  7. SELECT * FROM DELETED -- show data in Deleted virtual table
  8. end
In the bwlow
  1. --Now insert a new record in Employee table to see data with in Inserted virtual tables
  2. INSERT INTO Employee(EmpID, Name, Salary) VALUES(3,'Avin',23000)
  3. SELECT * FROM Employee

Deleted Virtual Table

The Deleted table holds the recently deleted or updated values means old data values. Hence old updated and deleted records are inserted into the Deleted table.
  1. CREATE TRIGGER trg_Emp_Upd
  2. ON Employee
  3. FOR UPDATE
  4. AS
  5. begin
  6. SELECT * FROM INSERTED -- show data in INSERTED virtual table
  7. SELECT * FROM DELETED -- show data in DELETED virtual table
  8. end

  1. --Now update the record in Employee table to see data with in Inserted and Deleted Virtual tables
  2. Update Employee set Salary=43000 where EmpID=3
  3. SELECT * FROM Employee
We could not create the virtual tables or modify the data with in the virtual tables. When we use the OUTPUT clause in SQL Server 2005, 2008 & 2012, then virtual tables are automatically created and managed by SQL Server.