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.

Creating Constraint on Table to check for data integrity

Creating Constraint on Table to check for duplicate values

Sometime we are asked to make sure that there is no duplicate data in our table based on certain columns combination. There are many ways to do this.

The first thing that will come to our mind is TRIGGER.

Well Trigger are good but its not always  best solution, particularly in our case here.

So let see how we can do it.

Create a table and insert some data.



CREATE TABLE [dbo].[Product](
[ProductID] [int],
[ProductName] varchar(100) NOT NULL,
[IsActive] Bit NOT NULL,
[RetiredDate] Datetime2,
[MinPrice] Float,
[MaxPrice] Float
)

Just to show how constraint works, I am not adding any Primary Key or Foreign Key relation with other tables.

Let's insert some data in this table.



  Insert INTO Product ([ProductID],[ProductName] ,[IsActive],[MinPrice] ,[MaxPrice])
      VALUES (101, 'Mountain-Bike', 1, 100.00, 500.00), (102, 'Mountain-rugged',1,299.99,999.99), (103, 'Mountain', 0, 199.99,399.99)

Check to make sure you have data in table you just inserted.


SELECT * FROM Product

Now let's say that our ProductID + ProductName+ IsActive has to be unique row in our table. So in this case based on this, there can be only two combination (0 or 1 for active and inactive values)

Suppose we do not want user to enter a value of above combination because we are considering this to be a unique values.



"101--Mountain-Bike--1"

Right now with no constraint, we will be able to enter this value.


 Insert INTO Product ([ProductID],[ProductName] ,[IsActive],[MinPrice] ,[MaxPrice])
      VALUES (101, 'Mountain-Bike', 1, 59.99, 199.00)

Here, I have just changed MinPrice and MaxPrice but productID, productName and IsActive are same, which should not be allowed if we want to maintain data consistency.


To prevent these type of data, we can create trigger based on unique combination of three columns where we want to maintain data integrity.


To do so, we have to remove last inserted row from our table. Let's go and remove that row.

Method 1:


   
ALTER TABLE dbo.Product
ADD CONSTRAINT unique_PID_PNAME_Active UNIQUE([ProductID],[ProductName] ,[IsActive])

Method 2:


CREATE UNIQUE INDEX uq_product
  ON dbo.Product([ProductID],[ProductName] ,[IsActive]);

Method 3: which is trigger (not recommended but you can still do it)

CREATE TRIGGER dbo.BlockDuplicatesproduct ON dbo.product

INSTEAD OF INSERT

AS

BEGIN

SET NOCOUNT ON;

IF NOT EXISTS (

SELECT 1

FROM inserted AS i

INNER JOIN dbo.product AS t ON i.productID = t.productID

AND i.[ProductName] = t.[ProductName]

)

BEGIN

INSERT dbo.product (

[ProductID]

,[ProductName]

,[IsActive]

,[RetiredDate]

,[MinPrice]

,[MaxPrice]

)

SELECT [ProductID]

,[ProductName]

,[IsActive]

,[RetiredDate]

,[MinPrice]

,[MaxPrice]

FROM inserted;

END

ELSE

BEGIN

PRINT N'';

END

END

GO;

Now test and see it works for you or not.