3

Using only microsoft based technologies (MS SQL Server, C#, EAB, etc) if you needed keep the track of changes done on a record in a database which strategy would you will use? Triggers, AOP on the DAL, Other? And how you will display the collected data? Is there a pattern about it? Is there a tool or a framework that help to implement this kind of solution?

GRGodoi
  • 1,946
  • 2
  • 24
  • 38

3 Answers3

2

Sql Server 2008 R2 has this built-in - lookup Change Data Capture in books online

Rikalous
  • 4,514
  • 1
  • 40
  • 52
2

The problem with Change Data capture is that it isn't flexible enough for real auditing. You can't add the columns you need. Also it dumps the records every three days by default (you can change this, but I don't think you can store forever) so you have to have a job dunping the records to a real audit table if you need to keep the data for a long time which is typical of the need to audit records (we never dump our audit records).

I prefer the trigger approach. You have to be careful when you write the triggers to ensure that they will capture the data if multiple records are changed. We have two tables for each table audited, one to store the datetime and id of the user or process that took the action and one to store the old and new data. Since we do a lot of multiple record processes this is critical for us. If someone reports one bad record, we want to be able to see if it was a process that made the change and if so, what other records might have been affected as well.

At the time you create the audit process, create the scripts to restore a set of audited data to the old values. It's a lot easier to do this when under the gun to fix things, if you already have this set up.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • If I well understood you are actually use this approach in a production environment system, do you? If true, the trigger approach is efficient in terms of writing speed? – GRGodoi Mar 16 '11 at 15:36
  • 1
    Yes we use in production. It's a medium sized database and it does slow down things a bit but not much (if you have correctly written the trigger code) for changes the user makes, it slows down our bulk imports (our bulk import are set up to not avoid the triggers) more but the user isn't going to notice that. We have regulatory requirements that dictate this approach, change data capture simply doesn't capture everything we need. We also use Change tracking but that is for a different purpose. – HLGEM Mar 16 '11 at 17:05
  • Wouldn't let me put in the size I consider a medium database, but our db using this process in prod is around 185 Gig and has thousands of users. – HLGEM Mar 16 '11 at 17:12
1

This is probably not a popular opinion, but I'm going to throw it out there anyhow.

I prefer stored procedures for all database writes. If auditing is required, it's right there in the stored procedure. There's no magic happening outside the code, everything that happens is documented right at the point where writes occur.

If, in the future, a table needs to change, one has to go to the stored procedure to make the change. The need to update the audit is documented right there. And because we used a stored procedure, it's simpler to "version" both the table and its audit table.

Tergiver
  • 14,171
  • 3
  • 41
  • 68
  • It's a bad idea (as is doing the same thing from the application) You cannot guarantee all changes will go through the stored procs (or application) and thus you have useless auditing. Do you think they go through the stored proc when a million records need to be updated because of some client change? Do you think the person fraudulently changing the records goes through the stored procs? Extremely bad choice, all auditing must be done at the database level. – HLGEM Mar 16 '11 at 17:09
  • There are no guarantees anywhere when it comes to databases. Sure, you can shore things up a bit, but there is never any way to stop people (and that includes future developers) from doing the wrong thing. If your application consistantly uses SPs, this works just fine. – Tergiver Mar 16 '11 at 18:14
  • No it doesn't it only appears to do so. And in production no more than 2 people should be able to change triggers (dba and backup dba) so you are far more likely to catch anyone who is making unauthorized changes which you cannot do at all using stored procs. It is extremely risky to audit the way you propose and no database specialist would allow it on their system. In 30 years I have never seen a database where data changes were not made outside the application on occasion. – HLGEM Mar 16 '11 at 18:21
  • I want to leave this answer here, in spite of the downvote, because I think it's useful. It's also useful to read the objection raised by @HLGEM. – Tergiver Mar 16 '11 at 18:53