You all probably know this already, but as you may also know I am generally not reticent to expose my relative ignorance.
So I follow a standard of adding four audit columns to my tables, populated by triggers, which keep track of who inserted/updated the row, and when:
I certainly did this for the qdb_users table, which is the users table for the PL/SQL Challenge and Oracle Dev Gym.
So far, so good.
But recently a player complained that she was not receiving emails with results of her quizzes. I checked and found that the preference was turned off. Had she modified her user profile lately or had my code done something to her row?
It was pretty much impossible to tell, because we keep track of the user's last visit to the site - which means the app itself updates the qdb_users.changed_by/on columns every time a user comes to the site. This would overwrite whatever the user's last changed_on value was.
I was using a "low level" audit column to also keep track of user-level behavior, with the result being a loss of information.
So I added a new column (changed_on_by_user), which is updated only when the user executes an action that updates his or her profile - all controlled through my PL/SQL API:
Lesson learned: don't mix system information (row-level audit information) and application/user information. Keep them separate, making it much easier (possible!) to track activity within your application!