Use Of Magic Tables While Updating Or Deleting Records

 

The Following Post Shows how Magic Tables Help In Creating Data Audit On Updation, Insertion and Deletion.

Lets Create Another Table with Similar Structure To Keep Audit Of Above Table

CREATE TABLE [dbo].[Testing_Audit](
    [Id] [int],
    [EmpId] [varchar](10),
    [EmpName] [varchar](20),
    [Gender] [char](1),
    [Designation] [varchar](20),
    [Department] [varchar](20), 
    [Salary] [money],
    [UpdatedOn] [datetime],
    [EncodedOn] [datetime]
) 

Now Lets See How Magic Tables Pushes Data To Audit Table. In The Following Update Query I Am Updating Name Of Employee To Sandeep Kr Verma From Sandep Kumar. In Typical Audit I should be able to get History of Records effecting by Update or Delete.
See the Following Example How History Is Maintained In Audit Table On Updating Record.

Update Testing  Set EmpName='Sandeep Kr. Verma' 
Output Deleted.*,GETDATE() into Testing_Audit
where Id=1

Look at the highlighted line above(Plays Major Role)  which fetches Data From “Deleted Magic Table” and Inserts Into Audit Table.

Select * From Testing;
Select * From Testing_Audit;

As You Can see Above Results From Testing And Testing_Audit Table. So these Magic Tables Can Be Used For Various Purpose.

See The Following Example For Delete Audit.

Delete From Testing
Output Deleted.*,GETDATE() Into Testing_Audit
where Id=2

Lets Check Our Audit Table

Select * From Testing;
Select * From Testing_Audit;

As You can See On Deleting Record From Testing Table Record Moved To Audit Table With the Help Of “Deleted” Magic Tables.