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.

ON UPDATE/DELETE CASCADE SQL SERVER

ON UPDATE/DELETE CASCADE


Let's explore CASCADE and see how it work.

USE Test2
GO

--Let's create a parent table Named Employee

IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'Employees' AND [type] = 'U')
DROP TABLE Employees
CREATE TABLE Employees
(
EmplID INT  PRIMARY KEY ,
FName VARCHAR(30),
MName VARCHAR(1),
LName VARCHAR(30),
Division VARCHAR(30)
)
GO

--Let's create a child table Named Employee Education

IF Exists (SELECT * FROM sys.objects WHERE name = N'EmployeeEducation' AND [type] = 'U')
DROP TABLE EmployeeEducation;
CREATE TABLE EmployeeEducation (
EducationID int IDENTITY (100, 1) Primary Key,
EmplID int, 
HighSchoolName Varchar(70),
HS_City Varchar(50),
YearPassed int,
UnderGraduate Varchar(70),
UG_City Varchar(50),
UG_YearPassed int,
Graduated bit,
)
GO

--Create Foreign Key relationship between Employee and EmployeeEducation Table

IF EXISTS (SELECT * FROM sys.objects where name = N'FK_EmployeeEducation_Employees' and [type] = 'F')
ALTER TABLE EmployeeEducation
DROP CONSTRAINT FK_EmployeeEducation_Employees
GO

ALTER TABLE EmployeeEducation
ADD CONSTRAINT [FK_EmployeeEducation_Employees]
Foreign Key (EmplID) References [Employees](EmplID)

--Time to Populate Employee Table


Insert INTO Employees 
SELECT 1,'David', 'D', 'Moore', 'IT' UNION ALL
SELECT 2,'AtulKumar', 'G', 'Patel', 'MKT' UNION ALL
SELECT 3,'Sanjay', 'C', 'Kumar', 'HR' UNION ALL
SELECT 4,'Telly', 'M', 'Morris', 'Purchasing'

Select * FROM Employees

--Time to Populate EmployeeEducation Table

Insert INTO EmployeeEducation
SELECT 1,'Saint Thomas High School', 'Dallas',1987,' BS Computer Science', 'Richardson', 1992, 0 UNION ALL
SELECT 2,'Saint Patrick Monroe School', 'Richardson',1988,' BS Artificial Intelligence', 'Plano', 1990, 1 UNION ALL
SELECT 3,'Saint Thomas High School', 'Plano',1989,' BS Computer Science', 'Irving', 1994, 1 UNION ALL
SELECT 4,'Saint Thomas High School', 'Irving',1990,' BS Computer Science', 'Wichita', 1995, 0 

Select * FROM EmployeeEducation


--Now try to Update Employee Table For 'David'

UPDATE Employees
SET EmplID = 100
Where EmplID = 1

--Msg 547, Level 16, State 0, Line 1
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeEducation_Employees". The conflict occurred in database "PK_Test", table "dbo.EmployeeEducation", column 'EmplID'.
--The statement has been terminated.


--Now Try to Update Employee Table

DELETE FROM Employees
Where EmplID = 2

--We get this error message
--"Msg 547, Level 16, State 0, Line 2
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeEducation_Employees". The conflict occurred in database "PK_Test", table "dbo.EmployeeEducation", column 'EmplID'.
--The statement has been terminated."

--BRING CASCADE ON

--Let's first drop FK Relationship

IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmployeeEducation_Employees' AND [type] = 'F')
ALTER TABLE EmployeeEducation
DROP Constraint FK_EmployeeEducation_Employees
GO

--Lets Add CASCADE OPTION to Our Constraints

ALTER TABLE EmployeeEducation
ADD CONSTRAINT [FK_EmployeeEducation_Employees]
FOREIGN KEY (EmplID) References [Employees](EmplID)
ON DELETE CASCADE ON UPDATE CASCADE 
GO

--Now try this time to Update Employee Table For 'David'

Do a Select of both Table.

Select * FROM  Employees;
Select * FROM EmployeeEducation;


This is what you will get.

Now run this UPDATE


UPDATE Employees
SET EmplID = 5
Where EmplID = 1

And now this will be the result:

--So we see that with Cascade On, we were able to update or delete.RIGHT!!!!

--Question is: Why do we want to USE or NOT USE CASCADE ON Option

-- The real use of CASCADE ON is when we have primary key which are not IDENTITY Column in many real world situation. For example Lets say we have 10 digit SSN number sitting somewhere in our government database. we have a master table which maintain SSN and thousands of other tables (banks, benefits, etc) which is referencing this column. Lets say we make a request to change our SSN from government. They obliged to change our SSN number (Well thank if they are able to do it!!!). In that case, lets assume that various bank agency,HR, and thousands of other departments need to be updated at the sametime.

--So by using CASCADE option for UPDATE, if we update SSN in main table, automatically it will be updated every other table it is being referenced.

If you use a natural key (e.g. a regular field from your database table) as your primary key, then there might be certain situations where you need to update your primary keys. Another recent example would be the ISBN (International Standard Book Numbers) which changed from 10 to 13 digits+characters not too long ago.

Follow MSDN for reference: Cascade on MSDN