Strategies for auditing table records
-
Hi all. I'm wondering if any of you would know of any articles and/or books regarding ways to keep track of who's been entering a particular record, modifying it, etc. I'm working on something right now that I would like to have something like it in place. I can think of putting a couple of fields, last_modified_by and date_of_mod, into each table which see the need for tracking, but that seems clunky. It would work well enough for this project as only a few people will be using it and it's not very big but when I start getting into the bigger stuff, I'm sure that will break down. So, if anybody knows of any good references, please, send it along. Thanks, Keith
-
Hi all. I'm wondering if any of you would know of any articles and/or books regarding ways to keep track of who's been entering a particular record, modifying it, etc. I'm working on something right now that I would like to have something like it in place. I can think of putting a couple of fields, last_modified_by and date_of_mod, into each table which see the need for tracking, but that seems clunky. It would work well enough for this project as only a few people will be using it and it's not very big but when I start getting into the bigger stuff, I'm sure that will break down. So, if anybody knows of any good references, please, send it along. Thanks, Keith
Keith Andersch wrote:
I can think of putting a couple of fields, last_modified_by and date_of_mod, into each table which see the need for tracking, but that seems clunky. It would work well enough for this project as only a few people will be using it and it's not very big but when I start getting into the bigger stuff, I'm sure that will break down
That is a start. I don't see why it would be an issue when you add more users to the system would be a problem, though. You might want to have a separate table for keeping the audit trail rather than adding fields to the existing tables.
-
Hi all. I'm wondering if any of you would know of any articles and/or books regarding ways to keep track of who's been entering a particular record, modifying it, etc. I'm working on something right now that I would like to have something like it in place. I can think of putting a couple of fields, last_modified_by and date_of_mod, into each table which see the need for tracking, but that seems clunky. It would work well enough for this project as only a few people will be using it and it's not very big but when I start getting into the bigger stuff, I'm sure that will break down. So, if anybody knows of any good references, please, send it along. Thanks, Keith
I have seen and used that strategy in many projects and it generally works well. On projects that require closer auditing, I use triggers to log changes to another database. That way, even admins changes through queries are logged. The data generated by this method is heavily taxing on database resources and may not scale well to very high transaction volumes (only load tests can show the truth). Scott
-
I have seen and used that strategy in many projects and it generally works well. On projects that require closer auditing, I use triggers to log changes to another database. That way, even admins changes through queries are logged. The data generated by this method is heavily taxing on database resources and may not scale well to very high transaction volumes (only load tests can show the truth). Scott
Scott Serl wrote:
On projects that require closer auditing, I use triggers to log changes to another database. That way, even admins changes through queries are logged.
Excellent point :-D