How to track number of changes occured in a column? T-SQL - SQL Server

Question

For example, i have a column named EmployeeName.

Every time a user changes/fix his name, i need to keep a count. If he changes his name twice, then count is 2. Also, i need to store the time of every change employee makes on EmployeeName e.g. if the name essentially is James and time created is 9:00 AM and then employee changes to John on 11:00 AM, i need to preserve this new time and new value as well as the previous one which is James at 9:00 AM.

I hope its clear! Thank you guys...Best Answer will be chosen...

If this requires a trigger, giving a sketchy pseudo-code will be very much appreciated.

Answer 1

A trigger will suffice. Triggers should be as simple and lightweight as possible, so just insert an entry into some logging table, and compute aggregates on it later.

Answer 2

In SQL Server 2008 you have the new Change Data Capture feature, you could use that to get the number of changes, plus the changes made: http://weblogs.sqlteam.com/derekc/archive/2008/01/28/60469.aspx

Sample Northwind code, as links can die over time:

EXEC Sp_cdc_enable_table 
  humanresources , 
  employee , 
  'HumanResources_Employee' , 
  1 , 
  dbo 


UPDATE humanresources.employee 
SET    ContactId = 1 
WHERE  employeeid = 1 

DECLARE @begin_lsn BINARY(10), @end_lsn BINARY(10)

SELECT @begin_lsn = sys.fn_cdc_get_min_lsn('humanresources_employee')
SELECT @end_lsn = sys.fn_cdc_get_max_lsn()

SELECT * FROM cdc.fn_cdc_get_all_changes_humanresources_employee(@begin_lsn, @end_lsn, 'all');

Answer 3

First up - if not already implemented, highly advisable to have employees identified by a permanent identifier (i.e. NOT EmployeeName) and so you can keep track on everything.

If you want to use a trigger, you could use an AFTER UPDATE trigger and look for a change to the name using if update(EmployeeName).

If that's been updated, you could increment the count column on the Employee table at the same time. Use the inserted table to identify those entries which have been updated. (Is the count essential? If you are storing a history of the name changes, I don't think it's necessary to have a count column - it's redundant informaiton.)

You would then add a row to your employee name history table that holds the details of this change with the current timestamp.