using triggers for audit columns

Question

In my company, i am a java developer and we use hibernate orm. Data Architects wanted audit columns ( CREATE_DATE, CREATED_BY, UPDATE_DATE, UPDATED_BY ) for every table on every database, because of data related purposes. This seems a bit weird, because we don't use these fields in our application business.

For our java applications, I guess, we have two options:

Making a base class with these audit fields on every hibernate dao object.
Defining table triggers to update these columns.

Which one should I prefer and why? Any other suggestions?

I'm extremely surprised the Data Architects gave you the choice. Audit columns should be set and updated by triggers, and not depend on the code to manage them. They are not for your code to use, or even be aware of, they are to audit row updates for forensic analysis if needed. — Jim Garrison, Dec 22 '17 at 06:17
Have a look at Hibernate Envers: http://hibernate.org/orm/envers/ — Erwin Bolwidt, Dec 22 '17 at 06:26
@ErwinBolwidt I know envers. Are you suggesting option 1 with envers? — irfangoren, Dec 22 '17 at 06:34
I'm suggesting that you use something that already exists rather than building it yourself. But "Which one should I prefer and why? Any other suggestions?" is "Primarily Opinion Based. Too Broad.". As you are asking it now, this isn't a good question for the SO format. Please read the [help] on asking good questions here. — Erwin Bolwidt, Dec 22 '17 at 06:50

score 0 · Answer 1 · answered Dec 22 '17 at 06:28

As per my understanding and experience , the 2nd option is the only good option out of the 2. This is because , in case the data gets updated/populated in the tables from outside the application (batch jobs/ data fixes etc.) , then the first option won't work.

Another option is , in case the data updates are happening through PL SQL only , your PL SQL team can change that code to update these columns in the code. However, this will be a lot of work in case the code is huge.

Overall, triggers seem to be the best choice.

using triggers for audit columns

1 Answers1