Use of UNPIVOT to create an AUDIT table

Question

I’ve have to insert new rows to a table when the data changes for one or more columns. When a new row is inserted I need to record the before and after image by column e.g. on a Person table the surname and phone number changes then in the Audit table I need to insert two rows one for the change to surname and one for the change to phone number showing the before and after data.

Using a trigger or an OUTPUT clause I can quickly identify the rows that have changed but then it seems I need an INSERT statement to the Audit table for each COLUMN that has changed. Which could be quiet expensive if the number of columns is large.

The only alternative I could think of, but could NOT get to work, was to try to create a table with the changes and then UNPIVOT them in a single statement.

So my PIVOT table would be something like:

CREATE TABLE myPIVOT
(
	myKey				INT,
	OldSurnameValue		VARCHAR(255),
	NewSurnameValue		VARCHAR(255),
	OldPhoneNumber		VARCHAR(255),
	NewPhoneNumber		VARCHAR(255)
)

And then UNPIVOT that to an Audit table like:

CREATE TABLE Audit
(
	myKey				INT,
	myColumnName			VARCHAR(255),
	OldValue			VARCHAR(255),
	NewValue			VARCHAR(255)
)

My question is then can you use UNPIVOT in this way?

Thanks

Answer 1

You could use CDC to accomplish this.  It would give that type of information, and then you could build a process to query the changes in the CDC tables.

Answer 2

SELECT myKey ,'SurnameValue' as myColumnName, OldSurnameValue as OldValue,NewSurnameValue as NewValue from myPIVOT
UNION ALL
SELECT myKey, 'PhoneNumber', OldPhoneNumber,OldPhoneNumber FROM myPIVOT

Order by mykey,myColumnName

Answer 3

I think I see how CDC could be used to address this problem, it is not something I have used before. Alas it is only available with the Enterprise Edition and we use a lesser version at work

Thanks for your help.

Answer 4

Thanks for your reply but your solution still means I am doing a select for each column which is what I was trying to avoid.

Answer 5

SELECT myKey, Replace(myColumnNameOld,'Old','') as myColumnName, NewVal, OldVal from myPIVOT  
UNPIVOT (NewVal For myColumnNameNew IN ([NewSurnameValue], [NewPhoneNumber])) unpvtNew
UNPIVOT (OldVal For myColumnNameOld IN ([OldSurnameValue], [OldPhoneNumber])) unpvtOld
WHERE Replace(myColumnNameNew,'New','') = Replace(myColumnNameOld,'Old','')
Order by mykey,myColumnName

Answer 6

If you want to compare historical values, you could do some sort of UNPIVOT with your insert.  I found a good dynamic UNPIVOT using XML and also using an exclusion table to filter out unwanted columns.  Take a look at this post; I think I used something very similar to the last post there.