How are transactions logs inserted?

Question

I have an ms-access database at work that has a table that's a transaction log.

The table contains the following fields:

Date - The date the transaction took place.

• Table_Name - Name of the table the field was changed in.
• Table_Field - The field in the table that was changed.
• Old_Value - The original value of the field (if any).
• New_Value - The new value of the field that was set during this transaction.

Now in a transaction log table like this, are these records normally set by the programming API of the system that modifies the database, a function of the database itself, or are they tiggered by something like a database trigger? Or does that depend on the rdbms that is being used?

Answer 1

Firstly, the correct term for this is an audit table (or maybe audit history). Transaction logging is something entirely different (that's a core part of the DBMS which logs all active transactions to guarantee the ACID properties) - the DBMS transaction log (which is in a binary, very-hard-to-read format) is reused once it's no longer needed ("once it's no longer needed" is a bit vague, but a full dissertation on transaction log semantics is a bit beyond the scope of this answer), where an audit history stays forever, as long as you don't clear the table.

To answer the actual question - it should be done inside the database (triggers being the easiest way). The basic reason for that is you want anything that's integral to the data (i.e. constraints, security rules, audit history etc.) to happen, no matter which application is using the database.

Additionally:

• What data type are you using for the Old_Value and New_Value fields?
• The style of audit table you're using there is going to lead to one row in the table for each field of each row you insert/update/delete, which means your audit history table is going to grow as fast as all of your other tables put together (and then some). You'll need to keep an eye on it to make sure it doesn't spiral out of control (I'd recommend clearing the table periodically - only after you've backed it up, of course).
• Another option (which I'd probably use if you need to refer to audit history frequently) is to have an audit table for each table in your database - so if you have Person(ID, Name), you'd have audit_Person(whoChanged, whenChanged, ID, Name), and then on update or delete, store the state of the row before the change (you don't need to do insert because you can just look in the table for the current state), along with the login of the user that changed it and the time.
  • This means no more audit table with 2 billion rows and makes querying for audit history significantly more intuitive (you get entire rows back in one piece, instead of having to go field-by-field) but does have the downside of duplicating schema changes (if you add a column to Person, you also need to add it to 'audit_Person'), and quite possibly being larger overall than the single-table approach.