How do you implement audit trail for your objects (Programming)?

Question

I need to implement an audit trail for Add/Edit/Delete on my objects,I'm using an ORM (XPO) for defining my objects etc. I implemented an audit trail object that is triggered on

1. OnSaving
2. OnDeleting

Of the base object, and I store the changes in Audit-AuditTrail (Mast-Det) table, for field changes. etc. using some method services called.

How do you implement audit trail in you OOP code? Please share your insights? Any patterns etc? Best practices etc? Another thing is that how to disable audit when running unit test,since I don't need to audit them but since base object has the code.

Changes to object (edit/add/del) and what field changes need to be audited

Novitiate

Answer 1

Database triggers are the preferred way to go here, if you can.

However, recently I had to do this in client-side code and I ended up writing a class that created a deep (value) copy of the object when it was opened for editing, compared the two objects at save time (using ToString() only) and wrote any changes to an audit table.

Edit: I had an [Audit] attribute on each property I wanted to consider auditable and used reflection to find them, making the method non-specific to the objects being audited.

Hap

I've voted this answer up, but one thing to watch out for when using database triggers is transaction brackets. You sometimes want to audit an event even when an error occurs and the transaction is rolled back. – Wampler

Answer 2

I don't know if it will fit seamlessly with your ORM, but i used Point-in-Time database design for an ERP application and really recommend it. You automatically get History and Audit from this architecture, as well as other benefits.

Besse

I am dealing with Multitenant application, where us all the tenant records will be stored in a single database with TenantId. If I use PTA , i will be ending up with hell lot of rows in the database, especially for update :( – Swaddle

Answer 3

I come more from the SW side that the DB side, if you create a set of DAOs (Data access objects) that you use for your interaction with the database. I would then insert the audit functionality into the respective functions in the DAOs that need to be trailed.

The database trigger solution is also feasible, it depends where you like to put your functionality, in the DB or in the code

There are a lot of ORM (Object relational Mapping) tools out there that create the DAO layer for you.

Holeandcorner

Answer 4

We've implemented a similar solution, using AOP (aspectJ implementation). Using this particular points can be captured and specific operations can be performed.

This can be plugged in and plugged off when we like.

If you really want to do it in the app layer, i would suggest this.

Hope it helps..

Vicariate

Do you have any sample or link? – Slattery

Answer 5

I've done this in Hibernate (another ORM) using an Interceptor for the Session. That way the audit code is seperate from your code.

Gizela

Answer 6

I know this doesn't answer your question, but for the record, I prefer to handle this type of auditing logic in the database.

Lorenzen

Answer 7

We have a table that all audit trail entries are stored in. A database trigger is on every table (it's put there by a stored procedure, but that's not relevant to this answer). When a value is changed, the old value is stored in the audit trail. Ours is a little complex in that we also have a look-up table that contains a list of every table we have, and another table that contains every field for each table. This allows us to look up an entry in audit trail, based on what table it's in by ID of that table in the first column. Then we also know exactly what field we are looking for based on the 2nd table's ID. This keeps us from having to store strings for the table name and the field name. To display it, our grids have an "audit trail" button next to the delete button. This opens a popup-grid with the history of that record. We use kendo grids, but none of this implementation is necessary for that. The popup is a bootstrap popup.

Longcloth