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?
I have used triggers to keep audit-logs of changes to certain columns in a table. For example, I want to know when a customer went "overdue" and then back to "active". The best place to create that log is in the database itself, since that also captures any manually updated rows (ie, those actions not initiated by the application code itself).
I have also used triggers to ensure data consistency and enforce state diagram transition rules for status columns in a table. These help capture logic errors in application code. For example, if your state diagram allows A -> B <-> C, then the trigger would disallow a transition from B or C to A, disallow A -> C, but allow C -> B and B -> C and A -> B.
To manage them, we treat them like all DDL changes: everything is done via SQL script, and those are tracked using our version control software, go through developer testing then staging testing, then finally production.
> I have used triggers to keep audit-logs of changes to certain columns in a table Another good use for triggers is to maintain customer balance..EG: An INSERT, UPDATE or DELETE involving a customer payment (or in the case of banks (deposit or withdrawals) would automatically maintain the balance in the customer master record.
