ACID transactional Actions

[poirazis]

The scenario : we often have to perform different DB operations on the press of a button or automation.
A great feature would be for a mechanism to exist to wrap these operations in a transaction, and only commit if all steps succeed.

This could be done by exposing a "Begin Transaction" and "Commit" Actions.

Just an idea!

[melohagan]

That's a nice idea! Would you mind sharing an example of one of your use cases?

[poirazis]

Hey @melohagan

Scenario : invoice -> invoice_items paradigm

Start Transaction

• Create New Invoice

• Get Invoice ID

• Add multiple invoice_items

On Save

• Do Some Calculations

• Commit Transaction ( Both invoice and invoice_items get commited )

On Cancel ( and in the future screen OnUnload )

• Rollback Transaction

Does this help ?

[melohagan]

Is this for the internal DB? I wonder if you were using SQL could you use an Execute Query action against a custom query that is using a BEGIN ... COMMIT

[poirazis]

If its a single Query , yes you could wrap in in BEGIN / COMMIT internally i guess.
But in the above scenario, I am talking about the case when you need to wrap various "Execute Query" Actions, in a single Transaction.
Another Scenario would be when you keep client side data, and want to make sure you push them all to the DB or none of it.
Assume we have an in-client-memory data store, where on a complex spreadsheet like form the user enters different values he moves things around etc without live saving.
In this scenario, in order to make sure you push the whole in-memory store to the DB you would wrap it in a transaction and loop over rows.

[JayP718]

Hm, I wonder if a stored procedure could satisfy this requirement. Although it's probably not available in each data source.

[melohagan]

Good thinking! Might do.