Long story short, I’m working on a web-based frontend that interacts with a database, and one of the functions is that every action on a particular table gets logged to keep a full history of all changes to that table.
An earlier attempt at using triggers in postgreSQL to handle the logging automatically ran afoul of a couple of other requirements of software that will use said database as a backend, so I’m back to manually creating the log entries and saving them.
My question is, what’s best practice? Creating and storing the log first, then making the change, or making the change first, then storing the log? I realize that it’s essentially a moot point because I’m wrapping the entire process into a transaction anyway, but I’m suddenly wondering if there are arguments in favor of either methods.
A log is traditionally something that records what has already happened (think ships’ log), so “act first log after” is the norm.
Although it makes little difference in your scenario (as you’re using transactions) that best practice is to do things the way that most developers would expect unless you have good reason not to (principle of least surprise).
0
Begin a transaction, make your changes, log the changes, then commit the transaction. If the changes fail, you are not logging them, and if the logging fails, you should probably roll back the transaction (depending on how important logging is in your system).
1
Actually the best practice would be to log the request, then commit, then attempt the update.
That way you have a record of the requested actions even if they failed.
If you are really paranoid you can log “success” or “fail” after you attempt the update.