Is this too much? An attempt to track field/column level changes.

Not to spoon feed you an example or anything (it was hard for me to find a complete one). But the following is a self-contained proof of concept, that will track specific column level changes.

I was surprised and impressed with the results.

--------------------------------------------------------------------------------
--// 1. Create/populat dummy table
CREATE TABLE [dbo].[Customer] (
[CustomerID] int IDENTITY(100000, 1) NOT NULL,
[LastName] varchar(35) NULL,
[FirstName] varchar(25) NULL,
[MiddleName] varchar(25) NULL,
[AddressLine1] varchar(55) NULL,
[AddressLine2] varchar(55) NULL,
[City] varchar(30) NULL,
[STATE] varchar(2) NULL,
[zip] varchar(15) NULL,
[HomePhone] varchar(15) NULL,
[CellPhone] varchar(15) NULL,
[WorkPhone] varchar(15) NULL,
[WorkPhoneExt] varchar(10) NULL,
[Fax] varchar(15) NULL,
[Email] varchar(60) NULL,
[datecreated] datetime NULL
)
GO

SET IDENTITY_INSERT [dbo].[Customer] ON
GO
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (123, 'Hall', 'Kira', 'A', '1 Big RD APT 29', '', 'PHOENIX', 'AZ', '85018', '123-654-1501', '--', '--', '', '--', '', '2011-02-01 20:59:17.563')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (322, 'Smith', 'Alan', 'B', '3 Water park wy', '', 'Big Rock', 'AR', '72205', '808-335-0003', '--', '--', '', '--', '', '2011-02-01 20:58:17.353')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (443, 'Lee', 'Eric', 'C', '5 Blue Lane', '', 'Portland ', 'OR', '97219', '--', '--', '--', '', '--', '', '2011-02-01 20:57:46.863')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (544, 'Marsh', 'Ben', 'D', '99 Flag ST', '', 'Orange', 'CA', '94014', '904-808-5433', '--', '--', '', '--', '', '2011-02-01 20:54:38.623')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (566, 'Green', 'Frank', 'E', '1 Disney Way', '', 'Anaheim', 'CA', '92886', '808-695-1314', '--', '949-808-3400', '', '--', '', '2011-02-01 20:51:54.997')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (577, 'Ronalds', 'Bob', '', '95 Cap St ', '', 'ARLINGTON', 'TX', '78582', '--', '--', '--', '', '--', '', '2011-02-01 21:50:33.833')
INSERT INTO [dbo].[Customer] ([CustomerID], [LastName], [FirstName], [MiddleName], [AddressLine1], [AddressLine2], [City], [STATE], [zip], [HomePhone], [CellPhone], [WorkPhone], [WorkPhoneExt], [Fax], [Email], [datecreated]) VALUES (588, 'Tool', 'Jennifer', 'G', '48 Bridges wy', '', 'Forest', 'AR', '71602', '555-808-0006', '--', '555-808-0000', '', '555-808-0100', 'fake@beans.com', '2011-02-01 20:51:29.257')
GO
SET IDENTITY_INSERT [dbo].[Customer] OFF

RETURN

--// Check table and new records
SELECT * FROM Customer

--------------------------------------------------------------------------------
--// 2. Created Fields Table
CREATE TABLE [CustomerField](
[CustomerFieldId] [int] IDENTITY(1,1) NOT NULL,
[CustomerFieldName] [varchar](128) NOT NULL,
[CustomerFieldDateCreated] [datetime] DEFAULT(getdate()) NOT NULL,
CONSTRAINT [PK_CustomerField] PRIMARY KEY CLUSTERED
(
[CustomerFieldId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--// Load Fields
INSERT INTO [CustomerField]( CustomerFieldName)
SELECT
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Customer'
AND COLUMN_NAME IN (
'CustomerId'
,'LastName'
,'FirstName'
,'MiddleName'
,'AddressLine1'
,'AddressLine2'
,'City'
,'STATE'
,'zip'
,'HomePhone'
,'CellPhone'
,'WorkPhone'
,'WorkPhoneExt'
,'Fax'
,'Email'
,'datecreated')
ORDER BY ORDINAL_POSITION
RETURN
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--// 3. Create Audit table
CREATE TABLE [dbo].[CustomerFieldAudit](
[CustomerFieldAuditId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[CustomerFieldId] [int] NOT NULL,
[CustomerFieldAuditValue] [xml] NULL,
[CustomerFieldAuditDateRecorded] [datetime] DEFAULT (getdate()) NOT NULL,
CONSTRAINT [PK_CustomerFieldAudit] PRIMARY KEY CLUSTERED
(
[CustomerFieldAuditId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CustomerFieldAudit] WITH CHECK ADD CONSTRAINT [FK_CustomerFieldAudit_CustomerField] FOREIGN KEY([CustomerFieldId])
REFERENCES [dbo].[CustomerField] ([CustomerFieldId])
GO
ALTER TABLE [dbo].[CustomerFieldAudit] CHECK CONSTRAINT [FK_CustomerFieldAudit_CustomerField]
GO
RETURN
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--// 4. Create table trigger
CREATE TRIGGER [dbo].[trg_Customer_UPDATE]
ON [Customer]
FOR UPDATE
AS
IF
(SELECT HASHBYTES('SHA1',
UPPER((
SELECT CustomerId
,LastName
,FirstName
,MiddleName
,AddressLine1
,AddressLine2
,City
,State
,zip
,HomePhone
,CellPhone
,WorkPhone
,WorkPhoneExt
,Fax
,Email
,datecreated
FROM
INSERTED
FOR XML PATH('')
)))
) <> (SELECT HASHBYTES('SHA1',
UPPER((
SELECT CustomerId
,LastName
,FirstName
,MiddleName
,AddressLine1
,AddressLine2
,City
,State
,zip
,HomePhone
,CellPhone
,WorkPhone
,WorkPhoneExt
,Fax
,Email
,datecreated
FROM
DELETED
FOR XML PATH('')
)))
)
BEGIN

INSERT INTO CustomerFieldAudit(CustomerId, CustomerFieldId,CustomerFieldAuditValue )
SELECT
CustomerId
,CONVERT(INT, RIGHT(FD_X,3)) FieldID
,FieldValue
FROM
(
SELECT
C.CustomerId
,CASE WHEN C.LastName <> N.LastName THEN (SELECT C.LastName FOR XML PATH('')) END FD_002
,CASE WHEN C.FirstName <> N.FirstName THEN (SELECT C.FirstName FOR XML PATH('')) END FD_003
,CASE WHEN C.MiddleName <> N.MiddleName THEN (SELECT C.MiddleName FOR XML PATH('')) END FD_004
,CASE WHEN C.AddressLine1 <> N.AddressLine1 THEN (SELECT C.AddressLine1 FOR XML PATH('')) END FD_005
,CASE WHEN C.AddressLine2 <> N.AddressLine2 THEN (SELECT C.AddressLine2 FOR XML PATH('')) END FD_006
,CASE WHEN C.City <> N.City THEN (SELECT C.City FOR XML PATH('')) END FD_007
,CASE WHEN C.State <> N.State THEN (SELECT C.State FOR XML PATH('')) END FD_008
,CASE WHEN C.zip <> N.zip THEN (SELECT C.zip FOR XML PATH('')) END FD_009
,CASE WHEN C.HomePhone <> N.HomePhone THEN (SELECT C.HomePhone FOR XML PATH('')) END FD_010
,CASE WHEN C.CellPhone <> N.CellPhone THEN (SELECT C.CellPhone FOR XML PATH('')) END FD_011
,CASE WHEN C.WorkPhone <> N.WorkPhone THEN (SELECT C.WorkPhone FOR XML PATH('')) END FD_012
,CASE WHEN C.WorkPhoneExt <> N.WorkPhoneExt THEN (SELECT C.WorkPhoneExt FOR XML PATH('')) END FD_013
,CASE WHEN C.Fax <> N.Fax THEN (SELECT C.Fax FOR XML PATH('')) END FD_014
,CASE WHEN C.Email <> N.Email THEN (SELECT C.Email FOR XML PATH('')) END FD_015
,CASE WHEN C.datecreated <> N.datecreated THEN (SELECT C.datecreated FOR XML PATH('')) END FD_016
FROM
INSERTED N
LEFT JOIN DELETED C ON N.CustomerId = C.CustomerId
) F
UNPIVOT
(
FieldValue FOR FD_X IN(
FD_002
,FD_003
,FD_004
,FD_005
,FD_006
,FD_007
,FD_008
,FD_009
,FD_010
,FD_011
,FD_012
,FD_013
,FD_014
,FD_015
,FD_016
)
) as UPV_FD_Value

END
RETURN
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--// Test out updates
--// Select top 10 * from Customer
UPDATE Customer
SET
FirstName = 'Bob'
,LastName = 'Mihada'
,WorkPhone = '808-808-808'
, Email = 'fake@uout.com'
,STATE = 'HI'
WHERE
CustomerId = 566

--//Review Audits
SELECT
A.CustomerId
,A.CustomerFieldID
,F.CustomerFieldName
,A.CustomerFieldAuditDateRecorded
,A.CustomerFieldAuditValue
,F.CustomerFieldName
,A.CustomerFieldAuditValue.value('(/)[1]', 'nvarchar(1000)') AS CustomerFieldAuditValue --Expensive
FROM
CustomerFieldAudit A
JOIN CustomerField F ON F.CustomerFieldID = A.CustomerFieldID

ORDER BY
A.CustomerId
,A.CustomerFieldID
,A.CustomerFieldAuditDateRecorded

/*
--// Clean up your mess
--// SELECT 'DROP TABLE ' + NAME FROM sys.objects WHERE type = 'U' and Name like '%Customer%'
DROP TABLE Customer
DROP TABLE CustomerFieldAudit
DROP TABLE CustomerField
*/

~ENJOY

Comments :

1
Bob Mihada said...
on 

Dang it, I found a flaw in my post. Sorry about that. but its pretty close and gets the head thinker thinking.