April 21, 2014 at 9:35 am
Hello. I want to log every rows in a table, like a versioning. For instance one user came and changed some fields and after for a while another user came and change same or different fields.
I want to see who, when and previous data of the row.
Is it possible to do that?
I just don't want to make a trigger for fields. Or if I do that what about the performance?
What would be your offers?
I'm using Sql Server 2008 R2.
Thank you.
April 21, 2014 at 9:40 am
You are talking about auditing in some way. There are a number of options, but all will affect performance. You can use
- triggers
- trace
- CDC/change tracking
- custom code in your application
There isn't necessarily an easy way to do this. Are you trying to do this for all tables or just one? Are you trying to capture all changes? Including LOB/BLOB changes?
April 21, 2014 at 10:15 am
Thanks for answer. I want to do that for all tables.
Seems Change Tracking is better way except if I won't do custom code in my application.
April 21, 2014 at 10:17 am
Change Tracking won't do what you've described. You would have to use CDC. Please note that CDC is only available on Enterprise Edition.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 21, 2014 at 2:41 pm
I second CDC. If you need a full history then CDC or a custom SQL audit (or several) is what you probably want. You wouldn't need to implement custom code in your app, you could setup jobs to query the CDC tables for audit info.
Joie Andrew
"Since 1982"
April 22, 2014 at 1:36 am
Ok, now I understand the difference between CT and CDC.
Thank you very much.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply