Creating history audit table data

Question

Creating history audit table data

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 1

Ray K (8/17/2010)
Brandie Tarvin (8/17/2010)
If you can piggy-back your logging table stuff on the code that actually inserts the original data into the table, that would be the best of all worlds, IMHO. ...
Okay, I see what you're saying. The only trouble is that the code that performs the original data insertion is on the application side and outside the database (I did not make that decision; if it was completely up to me, everything would've been done with SPs). The piece I'm working on needs to capture all data changes, regardless of whether it was done through the database or through the application. Unfortunately, I don't see how that can be done without triggers.

I know you're done coding, but I thought I'd throw this out here. If your data doesn't have to live in the same database, try transactional replication. Obviously, the costs would have to be compared to the trigger costs, though.

Weird morning for me. For some reason, I originally wanted to suggest using TR within the same database to a different table, having the db be both publisher and subscriber, but after I had that thought, I realized it might not be possible and that it's been so long since I've done Rep. that I need to seriously brush up. @=)

Questions for you, though. Are you just logging changes or do you need easy access to these changes? You might consider just logging the entire record in an XML datatype string for your audit table, regardless of what changed. Might be easier on your overhead.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

I think you have handled it pretty well if you are using one CHANGEID for each batch of changes.

I do like Brandie's idea of moving data to another database. It doesn't have to be replication, but I would build an archive plan for this change table and perhaps have it move data to a new database periodically if it grows large.

Ray K SSC-Dedicated Points: 31163 More actions · Answer 3

Brandie Tarvin (8/18/2010)
Questions for you, though. Are you just logging changes or do you need easy access to these changes? You might consider just logging the entire record in an XML datatype string for your audit table, regardless of what changed. Might be easier on your overhead.

Good question. We would need easy access, since we're going to run periodic reports on these tables. (One of my colleagues suggested we just rely on transaction logs; we rejected that for exactly this reason.)

As for the idea of saving it to a separate database, let me talk to my partners-in-crime and see what they think (since I'm not the only one working on this system, I'm going to need to confer).

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/