Re: When use triggers? - Mailing list pgsql-general

On Wed, May 16, 2018 at 6:36 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 05/16/2018 03:19 PM, hmidi slim 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?
>
>
> Two benefits triggers and their associated functions offer, with the
> understanding these are general statements:
>
> 1) Are in the database so tasks that always need happen do not need to be
> replicated across the various interfaces that touch the database.
>
> 2) Also since they run on the server the data does not necessarily cross a
> network, so less network bandwidth and better performance.
>
> An example of a use case is table auditing. If you need to track what is
> being INSERTed/UPDATEd/DELETEd in a table stick an audit trigger/function on
> the table and push the information to an audit table.
>
> As to managing, they are like any other code. I keep my schema code in
> scripts under version control and deploy them from there. I use
> Sqitch(http://sqitch.org/) which allows me to deploy and revert changes. I
> use a dev database to test and troubleshoot triggers and functions.
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

The only appropriate use for triggers that I've ever found was for
auditing changes to tables.

It can be quite trivial for simple cases: just use an update trigger
and write all of the "deleted" (e.g., before the update)
values/columns that you want to track to your audit table.  Each row
in the audit represents the previous state before changes were made.
It's so trivial that you could write a sql script to generate a
simplest-case audit table and audit trigger for any table where you
don't need any custom behavior in the audit logic

As for management, you could also have a build or deployment task that
updates audit tables and the triggers as columns are added (or perhaps
removed) from their related tables (e.g., semi-automatic management).

To keep them lightweight, my typical audit table is insert-only (other
grants are removed) with a primary key, no foreign keys, and a single
nonunique index on the main table's primary key if the audits related
to a main-table row need to be viewable by an end user or something
similar.

It's kind of interesting that you speak of microservices and triggers.
If you go "all in" with microservices, the typical approach is to use
event sourcing and CQRS and then all of your writes __are__ your
audit.

~Mike Stephenson