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.
- What data type are you using for the
- 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.