question

JustABitOfCode avatar image
JustABitOfCode asked

SQL Server 2008 Auditing

Hi all,

I'm testing the Audit functionality in SQLServer 2008 Enterprise, but it looks like the data that's stored for an update doesn't include the value of the column before and after the operation.

This is pretty fundamental to my audit requirements.

For example, I've set up Auditing on my Customers table and when I make an update to a row using something like

UPDATE Customers SET MaritalStatus = 1 WHERE CustomerID = 555

The audit data says that I made the change and when I made it, but doesn't say what row was affected, what data was changed and what it was changed to.

At first glance, it looked like the perfect solution, but now looks useless.

Have I missed something?

sql-server-2008audit
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Christopher Klein avatar image
Christopher Klein answered

you could always do something like; 


Update Customers
set MaritalStatus =1
output inserted.CustomerID, deleted.maritalstatus,inserted.maritalstatus,getdate(),suser_name()
into AuditCustomerChanges
where CustomerID=555

Just make a table like AuditCustomerChanges and that would track everything, wouldn't it?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.