Dear pgsql hackers,
I am Gianluca Calcagni, a Salesforce Certified Technical Architect. In the course of my career, I accumulated extensive experience working with triggers, both as an architect and as a developer.
When I work with multiple developer teams, they often need to create triggers on the same object: when that happens, I usually recommend them to maintain a single common trigger rather than multiple ones - the rationale being that, by sharing the code, they can minimise most "negative" interactions, such as recursion, trigger cascades, for-loops, multiple DMLs on the same record, and so on. In the Salesforce ecosystem, such approach is considered a best practice (for good reason).
The real drawback is that such approach is forgoing the natural principle of "separation of concerns"! I have been looking into using trigger frameworks to solve this problem, but there is no trigger framework that is able to meet my main expectations: in short, isolation and conflict detection.
This is what I want to see:
- when an event is firing two or more triggers, then such triggers must be executed within the same transaction, but each trigger must be executed in its own isolated context (in the sense that it cannot see the changes applied by any trigger other than itself)
- when all the triggers are done, postgres must merge the results together and stage an end-result
- postgres must raise all possible conflicts (in the sense that, if a specific field on some record has distinct changes applied by different triggers, then the end-result of the transaction is ambiguous hence the entire transaction should fail)
- If everything goes fine, the end-result of the transaction is finally committed to the database.
You may notice that I took inspiration from GIT for most of the concepts above (e.g. "isolation" actually means "forking").
I realize that this is a huge request, but I believe there is some merit in the idea. I also realize that there are very complicated implementation nuances (e.g. in handling foreign keys and constraints), but I am happy to provide more input about my vision of this feature.
Looking forward to your feedback!
Wish you all a great day,
Gianluca