Move multiple trigger functions to single definition for transactions_transaction

[exalate-issue-sync]

Currently, we have multiple trigger AFTER INSERT and/or UPDATE trigger functions defined against the transactions_transaction table. This ticket is to replace these multiple functions with a single AFTER INSERT OR UPDATE function that will then call into the other unique implementations.

Multiple trigger functions defined on a single table can be problematic for several reasons including because the order they execute in is not always clear (they are in-fact executed alphabetically in Postgres which isn’t obvious). Because of this, the following trigger function had to be prepended with a z to ensure it is always executed after calculate_aggregates:

zafter_transactions_transaction_insert_or_update_trigger

Combining these functions will also aid in adding the ability to roll back deployed trigger definitions if needed and also will make debugging trigger behavior easier while simplifying our implementation and potentially improving performance.

QA Notes

Unit test coverage being leveraged for validation

DEV Notes

The current triggers are defined in several migrations and currently include the following statements:

{{CREATE TRIGGER transaction_updated}}

{{CREATE TRIGGER calculate_aggregates_trigger}}

{{CREATE TRIGGER zafter_transactions_transaction_insert_or_update_trigger}}

At least these should be considered for consolidation.

Recommend redefining the {{after_transactions_transaction_insert_or_update}} function to include calls to the other functions as needed and removing the other trigger definitions altogether.

We will also need to ascertain the need for new/additional unit tests on the various triggers.

Design

See full ticket and images here: FECFILE-1899