We would like to use Smart Service to delete records from the DSE (Data Store Entity) and create records according to the input received from the screen.In this case, will the Delete and Write transactions be separated?For example, if Write after Delete fails for some reason, I would like the Delete transaction to rollback.
Please let me know if there is a way to execute Delete and Write in the same transaction on Smart Service.
Discussion posts and replies are publicly visible
You have to use two different services for the same. For your use-case, you can create a process where you always keep a backup of what was deleted and what was inserted so you can insert the deleted ones and delete the inserted ones if the process fails.
Thank you for your reply. I'm thinking about query database, what about this？
Query Database smart service? Why you want to use that?
Alternatively you can write a Stored Procedure that conducts both Write and Delete operations in the scope of a single transaction and then invoke that from Appian. You'd need to detect any failures/rollbacks issued from the Stored Procedure and handle this in your process model so that the appropriate notification(s) and action(s) can be conducted.
I my experience, it is a better approach to not make the database manage a transaction, but to look at the Appian process to be that instance.
My question is just one of the examples. We have multiple tables that need to be deleted or written at the same time. We've been thinking about Write to Multiple Data Store Entities but it seems to only allow writes, not deletes. So we have to separate delete and write in two. Then the deleete operation will not be recovered even if the later performed write operation fails. So I'm considering using Query DataBase to do both in one node. When one of the tasks in the node fails it returns an error and all the tasks in the node are not executed.
Thanks for the answer. Is there any concrete example of how to use it on Appian. I didn't find much useful information on how to use it nor how to feed information from the process variable to it.
Thanks for the answer. I understand what you mean but in order to fulfill the request from the client we need to create many different tables to manage the data. I'm a little nervous when doing data write or delete in tables without transaction manager.
Probably for the first time ever I have to disagree with you. Here's why;
Until we get native "Begin Transaction" , "End Transaction", "Rollback Transaction" commands that can bracket multiple Smart Services in an Appian Process then by far the safest and reliable method is to use the DB's native functionality and incorporate this in a Stored Procedure.
docs.appian.com/.../Execute_Stored_Procedure_Smart_Service.html