How to decouple the transactions between inserting a record into a table and subsequent DB actions in its post-insert trigger

No-Code Platform
1

I have a table that has a post-insert trigger that will call a logic block after the insert. I noticed that if there is a database transaction error within that logic block, the record in that table would not be committed. How can I allow the record in the table to be committed even there is a transactional error in the post-insert trigger of logic block?

2

There’s many different ways to accomplish this goal, none of them directly involving logic within a Post-Trigger logic block. The post-trigger runs after the record insert in the commit cycle but still within the same database transaction, so a failure within the post-trigger logic will cause a rollback of everything involved in that transaction. I will detail three methods below, but if none of these work for your use case, please provide some more information about the scenario.

[Easiest] Use the post-trigger logic block to call another logic block in a new database transaction:

  • If there’s logic in the post trigger that should occur within the main transaction, perform that logic first
  • After all necessary logic and validations, call a new logic block with the Run in new database transaction checkbox selected, and include the post-trigger logic in that child logic block

Use workflow on the table to perform the logic instead (applicable only if the table in question is governed by workflow):

  • Using an autotransition from a New to Posted state, for example, you can include a Conditional logic block or Post-Run logic block. Since this autotransition runs in a separate transaction (via Workflow Instances), any failures can cause the record to proceed to an Errored state while still committing the insert
  • If you instead use the Queue Workflow Transition logic action and a Logic block controlled transition in the workflow definition, you will run into the same problem as the post-trigger issue because this runs in the same transaction
    • Note: it is not a good practice to use a Post-Run logic block during autotransitions for this purpose because providing error visibility to the user is very difficult

Use a background task (applicable only if the logic you need can run asynchronously instead of live):

  • In the table’s API or the mentioned Post-Trigger, queue a background task logic block that contains the logic you would prefer to run separately from the table insert
  • The background task will run in the Jobs queue and is not guaranteed to happen immediately.
  • If you utilize the Queue Immediately checkbox, which will begin the task immediately, you can initialize the background task immediately and without regard for the main transaction. This means that the background task will execute even if the main insert fails for another reason (after queueing)
  • If the background task logic fails, the triggering user will receive a notification with the associated error but the insert will still commit
    • Note: this option should only be implemented if you also implement a means of retrying the logic contained in the background task. When the user encounters an error, that user should be able to select a row action, save again, or some other means of spawning a new background task
2 Likes
3

Ah, the 1st option will work for me. That means I have to move all the existing logic to another logic block and call it from the post-insert logic block with the Logic Block Ceate Call Mode. Thanks.

1 Like