SQL Server: Keeping track of changing data in a table

Sometime we are asked to keep a track of how data are changing over time in a table. This is critical in DW world. If they have (most of the time, they have Enterprise Edition so they can implement CDC method).

However there are some simple method we can use to keep track of data change for Insert/Update/Deleted.


Let's see how we can do this.

Steps 1. Let's create a table where we want to keep track of data change.



SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [UserTable](
ID [int] IDENTITY(1,1) NOT NULL,
FName varchar(20) NOT NULL,
LName varchar(20) NOT NULL,
Address1 varchar(40),
Address2 varchar(40),
City varchar(20) NOT NULL,
ZipCode varchar(10),
[StateCode] varchar(2) NOT NULL,
CreatedDate Datetime NOT NULL,
CreateBy int,
UpdatedDate Datetime NOT NULL,
UpdatedBy int



 CONSTRAINT [PK_UserTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO


Step 2.Let's create a archive table where we will keep track of all the data that is changing in above table.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [UserTable_Archive](
[ArchiveID] [bigint] IDENTITY(1,1) NOT NULL,
[ArchiveDate] [datetime] NOT NULL,
[ArchiveAction] [varchar](3) NOT NULL,
ID [int]  NOT NULL,
FName varchar(20) NOT NULL,
LName varchar(20) NOT NULL,
Address1 varchar(40),
Address2 varchar(40),
City varchar(20) NOT NULL,
ZipCode varchar(10),
[StateCode] varchar(2) NOT NULL,
CreatedDate Datetime NOT NULL,
CreateBy int,
UpdatedDate Datetime NOT NULL,
UpdatedBy int
 CONSTRAINT [PK_ArchiveID_Archive] PRIMARY KEY CLUSTERED
(
[ArchiveID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [UserTable_Archive] ADD  CONSTRAINT [DF_UserTable_ArchiveDate]  DEFAULT (getdate()) FOR [ArchiveDate]
GO



--Step 3. Let's write trigger for Update/Delete/Insert


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



create trigger UserTable_Archive_Delete
on UserTable for delete

as


insert UserTable_Archive (
 [ArchiveDate]
, [ArchiveAction]
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy
)
select
 getdate()
, 'DLT'
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy

from deleted

GO

create trigger UserTable_Archive_Insert
on UserTable for Insert

as


insert UserTable_Archive (
 [ArchiveDate]
, [ArchiveAction]
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy
)
select
 getdate()
, 'INSERT'
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy

from INSERTED

GO




create trigger UserTable_Archive_Update
on UserTable for Update

as


insert UserTable_Archive (
 [ArchiveDate]
, [ArchiveAction]
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy
)
select
 getdate()
, 'UPD'
, ID
, FName
, LName
, Address1
, Address2
, City
, ZipCode
, [StateCode]
, CreatedDate
, CreateBy
, UpdatedDate
, UpdatedBy

from Inserted

GO

Step 4:  Let's insert some data and see how it is implemented.

Insert into UserTable (FName ,LName,Address1 , Address2 , City , ZipCode , [StateCode], CreatedDate , CreateBy, UpdatedDate , UpdatedBy ) Values ('Mary','Stephon', '2250 Rock Road', '231', 'Dallas', '123456', 'TX', getdate() , 0, getdate(), 0)
Insert into UserTable (FName ,LName,Address1 , Address2 , City , ZipCode , [StateCode], CreatedDate , CreateBy, UpdatedDate , UpdatedBy ) Values ('Mary','Jane', '2251 Rock Road', '231', 'Dallas', '123456', 'TX', getdate() , 0, getdate(), 0)
Insert into UserTable (FName ,LName,Address1 , Address2 , City , ZipCode , [StateCode], CreatedDate , CreateBy, UpdatedDate , UpdatedBy ) Values ('Mary','Simon', '2252 Rock Road', '231', 'Dallas', '123456', 'TX', getdate() , 0, getdate(), 0)
Insert into UserTable (FName ,LName,Address1 , Address2 , City , ZipCode , [StateCode], CreatedDate , CreateBy, UpdatedDate , UpdatedBy )Values ('Mary','Colbert', '2253 Rock Road', '231', 'Dallas', '123456', 'TX', getdate() , 0, getdate(), 0)


Select * FROM UserTable;
Select * from UserTable_Archive;

Similary Let's do some update/Delete to existing data


Update UserTable
Set FName = 'Lisa'
Where LName = 'Jane'

Delete UserTable
where LName = 'Colbert'

And then let's see how data looks

Select * FROM UserTable;

Select * from UserTable_Archive;

There you go. However there is some cost associated with this method.