Tracking changes of a table using trigger

Audit Table data for new values old values

Requirement: I have a table and the data of that table is getting change very frequently. I need to trace the all change of that table so I can know at any point of time what values got changed and what was the values before also some time you need to know after deletion of data what was the data I have deleted .

Below is my employee table

Id	Name	Address1	Adress2	Sal
1	Amit	Dehradun	Gaya	12000
2	Praveen	Gaya	Delhi	19000
3	Sachine	Gaya	Banglore	20000

    

AuditTable

Id	Column name	Old Values	New Values	AuditDate
1	Name	Amit	Amit kumar	12 jan 2014
1	Address2	Gaya	Aurangabad	17 jan 2014

Solution: I have created trigger on all Insert, Update, and Delete event on employee table.

  USE [exp]

GO

/****** Object:  Table [dbo].[emp]    Script Date: 3/12/2014 7:53:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[employee](

[id] [int] NULL,

[name] [varchar](100) NULL,

[address] [varchar](100) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

USE [exp]

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (1, N'Amit', N'Gaya himachal')

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (2, N'Suresh', N'panjab')

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (3, N'Sachine', N'Banglore')

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (4, N'Bubly', N'Delhi')

GO

INSERT [dbo].[employee] ([id], [name], [address]) VALUES (5, N'Praveen ', N'Gaya')

GO

USE exp

go

alter TRIGGER dbo.tgr_employee 

ON  [dbo].[employee]

AFTER update

AS 

BEGIN

select a.id,a.ColumnName,a.VALUE as newValues,b.VALUE as OldValues from (

SELECT id,ColumnName,value FROM 

  (SELECT ID, NAME,address FROM INSERTED) p

UNPIVOT

  (VALUE FOR ColumnName IN  (NAME,address))AS unpvt

) a 

INNER JOIN 

(

SELECT id,ColumnName,value FROM 

   (SELECT ID, NAME,ADDRESS FROM deleted) p

UNPIVOT

   (VALUE FOR ColumnName IN  (NAME,ADDRESS) 

)AS unpvt

)b on a.id=b.id and a.ColumnName=b.ColumnName 

 and  a.VALUE<>b.VALUE

END 

GO

SELECT * FROM DBO.employee

UPDATE DBO.employee SET name='Sumit' WHERE ID=5                       

Here you will get result set , you just have to write insert statement in the trigger as per requirement to load data in your audit table.