So, today I had a discussion with a colleague about whether it was a good idea to store business logic in a database trigger. My default position, probably because I’m an application developer has always been to keep any logic in the application and just use the database to store data.
The discussion in particular was around whether to use a database trigger to copy comments into an audit table and then remove the comments from the current table, essentially moving them at a particular point in the workflow. The thing I don’t like about this is the ground is moving beneath your feet and in certain situations the data will disappear, which a few months/years down the road could waste time in working out what is happening. The justification for this change was because it’s easy to do it there. My point was that you can just as easily write that in the application and also write automated tests as part of the build to verify it’s working. True, you could probably write SQL tests as well to prove the trigger.
What do others think?
It got me thinking that I should really see what the general consensus is in the industry before I push hard either way. It seems that a lot of people are divided, and maybe because some are database developers and the others are application developers.
The question was raised here on stackexchange and the “answer” pretty much came back as don’t do it. This made me think, yes, I was right . However, looking further down the list of responses there is some middle ground.
Some people use databases as simple key/value pair type systems and from my perspective this isn’t a bad thing. You don’t have to have a specialist DBA, if performance of the operation isn’t critical then keep it in the application and then all the logic is in one searchable place (developers often don’t think to look in the database for . You don’t end up with vendor tie-in with your database provider which although rare, organisations do switch DB systems.
The arguments for putting things into the database (triggers specifically) from this article. They say that the problems are with the triggers, they are with the developers. Pushing the onus onto the skill of the developer etc. There are some capabilities that databases provide like auto-generated columns etc. where triggers and stored procedures come in.
I think the conclusions aren’t clearcut, the opinion in a couple of places seems to be that they generally aren’t great for the reasons I gave above. It depends a lot on usage but I still stand by my principles of ensuring that software should be easy to understand, you shouldn’t have to hunt around many different systems to see what’s going on and only do something in other ways if there is a compelling need, i.e. performance etc.
In the situation described above, I do believe though that auditing is a good use case for database trigger and I said that in the end to wrap up the discussion. I think it is important to question and have an opinion on development matters, however, it is also important to step back when it is a suitable way and learn from different points of view.