I've always found it most useful to consider the difference between "what the system is" vs. "what the system does". The core data entities and their stable relationships comprise most of what the system is. These are the things that should be enforced at the lowest level possible (in a db schema) and can be supported by other tools preferably as close to the database as possible - such as triggers and stored procedures. The purpose of these items should be strictly regulated to keeping the integrity of these entities intact. They should not presume a particular usage model nor should they implement application-specific business logic (which is something that changes more frequently).
The various applications and businesses logic comprise "what the system does". Those entities now play various (often dynamic) roles in the context of your use cases. This is what should be implemented in your various programming languages and should be independent of any specifics of your database (or any particular UI as well if possible). This makes testing the application much easier and your application is also easier to write if it can assume that integrity constraints are already enforced at a lower level by the DB itself. This business functionality generally should not be implemented within the DB as it tightly couples the DB and the app which gets really painful as the app evolves.
Hope that general set of policies helps guide your thinking and makes your efforts more effective.
- - Ben Scherrey
I'm working on a microservice application and I avoid using triggers because they will not be easy to maintain and need an experimented person in database administration to manage them. So I prefer to manage the work in the application using ORM and javascript.
However I want to get some opinions and advices about using triggers: when should I use them? How to manage them when there are some problems?
