Header Ads

Magic Tables

What is Magic Tables?
Magic Tables are invisible tables which created on MS SQL Server, during INSERT/UPDATE/DELETE operations on any table. These tables temporarily persists values before completing the DML statements.

sql-magic-table

These Magic tables is used In SQL Server 6.5, 7.0 & 2000 versions with Triggers only while in SQL Server 2005, 2008 & 2008 R2 Versions can use these Magic tables with Triggers and Non-Triggers also. Magic tables with Non-Trigger activities uses OUTPUT Clause in SQL Server 2005, 2008 & 2008 R2 versions

Magic Tables are internal table which is used by the SQL server to recover recently inserted, deleted and updated data into SQL server database. That is when we insert or delete any record from any table in SQL server then recently inserted or deleted data from table also inserted into inserted magic table or deleted magic table with help of which we can recover data which is recently used to modify data into table either use in delete, insert or update to table. Basically there are two types of magic table in SQL server namely: INSERTED and DELETED, update can be performed with help of these twos. When we update the record from the table, the INSERTED table contains new values and DELETED table contains the old values. Magic Tables does not contain the information about the Columns of the Data Type text , ntext or image. These are maintained by SQL Server for internal processing whenever an update,insert,Delete occur on table.

Suppose we have Employee table, Now We need to create two triggers to see data with in virtual tables Inserted and Deleted and without triggers.

For INSERTED virtual table, using Trigger ( Magic Tables with Triggers)
CREATE TRIGGER trg_Emp_Ins ON Employee
FOR INSERT
AS
begin
/* Here you can write your required codes, but I am here putting only demostration of Magic Table.*/
Print ‘Data in INSERTED Table’
SELECT * FROM INSERTED — It will show data in Inserted virtual table
Print ‘Data in DELETED Table’
SELECT * FROM DELETED — It will show data in Deleted virtual table
end
–Now insert a new record in Employee table to see data with in Inserted virtual tables
INSERT INTO Employee(Name, BasicSalary) VALUES(‘Navaskhan’,2000)
For INSERTED virtual table, without Trigger ( Magic Tables with Non-Triggers)
— Use INSERTED Magic Tables with OUTPUT Clause to Insert values in Temp Table or Table Variable
create table #CopyEMP(CName varchar(12),CDept varchar(12))
–Now insert a new record in Employee table to see data with in Inserted virtual tables
Insert into Employee(Name, BasicSalary) OUTPUT INSERTED.Name, INSERTED.BasicSalary into #CopyEMP values(‘navas’,‘100’)

Select * from #CopyEMP
For DELETED virtual table, using Trigger ( Magic Tables with Triggers)
CREATE TRIGGER trg_Emp_Ins ON Employee
FOR DELETE
AS
Begin
/* Here you can write your required codes, but I am here putting only demostration of Magic Table.*/
Print ‘INSERTED Table’
SELECT * FROM INSERTED — It will show data in Inserted virtual table
Print ‘DELETED Table’
SELECT * FROM DELETED — It will show data in Deleted virtual table
End
–Now Delete few records in Employee table to see data with in DELETED virtual tables
DELETE Employee where BasicSalary > 10000
For DELETED virtual table, without Trigger ( Magic Tables with Non-Triggers)
— Use DELETED Magic Tables with OUTPUT Clause to Insert values in Temp Table or Table Variable
create table #CopyEMP(CName varchar(12),CDept varchar(12))
–Now Delete record in Employee table to see data with in DELETED virtual tables
DELETE Employee where BasicSalary > 10000

Select * from #CopyEMP

No comments:

Powered by Blogger.