Transaction Mangement in a Recipe

[lalatendujena]

I have the following use case:

I am calling 5 different APIs to retrieve data from 5 different tables in a database. Once I receive the responses from all the tables, I need to push those records into 5 corresponding tables in a SQL Server database in another system.

I am currently able to handle this within a single recipe and can insert/update records in SQL Server successfully.

However, I would like to implement a rollback mechanism in case any one of the table updates fails. For example, if 4 tables are updated successfully but the 5th table update fails for some reason, I want to roll back all the insert/update operations that were performed on the other 4 tables.

Any suggestions would be greatly appreciated.

[shivakumara · Accepted solution]

Hi @lalatendujena ,

It would be better to implement a stored procedure to handle the updates across all required tables. This allows the entire operation to run within a single database transaction.

By using transaction control (for example, IF @@TRANCOUNT > 0 ROLLBACK TRAN in case of SQL Server), we can ensure that if an update to any table fails, the complete transaction is rolled back. This maintains data consistency and prevents partial updates.

This approach is recommended because the Oracle/SQL Server connector does not provide a native rollback mechanism at the action level, so managing the transaction within the database is the most reliable and controlled solution.

Thanks and Regards,
Shivakumara K A

[lalatendujena]

HI @shivakumara ,

Thank you, I appreciate your input. Yes, I believe this is the right approach to handle it on the SQL Server side, as it should be more reliable. I will try to implement this and let you know if I run into any issues.

I just wanted to check if, by any chance, it’s possible to handle this scenario from the Workato side instead.

[shivakumara]

Hi @lalatendujena ,
The above mentioned solution holds good for Workato also. You can use Execute stored procedure action.