Re: When use triggers?

On Wed, May 16, 2018 at 6:19 PM, hmidi slim <hmidi(dot)slim2(at)gmail(dot)com> wrote:

> HI,
>
> 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.