My fellow developers and I are at a crossroads in how to go about continuing our auditing of database changes.
Most of our applications log changes via INSERT, UPDATE, and DELETE triggers.
A few of our newer applications audit at the ORM layer; specifically using Hibernate Envers.
While ORM layer auditing provides a much cleaner interface and is much more maintainable, it will not capture any manual database changes that are made. ORM layer auditing also means that our libraries will currently require a dependency on our ORM implementation unless, specifically in our case for example, JPA plans on providing something in the near future.
Is there a common paradigm that addresses this?
ORM layer auditing also means that our libraries will currently require a dependency on our ORM implementation unless
That’s Right. you want all your business logic in one place, including logging.
it will not capture any manual database changes that are made
Unless you manually log the Changes … If you are ok with making data entries manually (bypassing your business layer) I see no reason why making manual Log entries would bother you.
Lastly, there is the option to duplicate logic in the Trigger. Have a field that captures the Source of the Data, so you will know what entries where made manually (and by whom) an which was by the app. (This is a good idea regardless of your logging problem). So you can code the trigger to fire only for Manual updates. (This is a Horrid Solution… But it is viable)
1
There’s a paradigm that addresses this. It’s called “triggers”.
If there are multiple people and systems that can touch the data outside the control of your code, then the code can’t log those changes. You need it in a layer close to the data so it won’t be bypassed.
Is this a violation of layers? Maybe. But so are primary and foreign keys. They enforce things at a layer close to the data that could theoretically be enforced by code exclusively. Keep an open mind and don’t follow a particular design method as gospel.