How to maintain Atomicity in Appian - while Persisting Transactional Data

Question

Hi Team, 
 I was trying to use multiple Data Store Entities for maintaining Atomicity in Storing Transactional Data to Database. 
 But i was not able to achieve this, was still inserting/updating in 2nd Entity even if the 1st entity was empty. 
 We have to insert/update in Multiple entities for a process Model or by Using a Web API. If in one entity it fails to insert/update then it should not make any transaction or Rollback to the previous state. 
 Please help me with this on how to achieve Atomicity in Appian. 
 Thanks 
 ejass

dipaki · Accepted Answer

Hi ejass , 
 
I was having the same kind of requirement. In such cases I would suggest to use Stored procedure in database and call execute the stored procedure using executestoredprocedure() plug-in function. Write your insert queries in between START TRANSACTION and COMMIT/ROLLBACK 
 
BEGIN 
 
 ... 
 
 START TRANSACTION; 
 
 ..statement 1.. 
	..statement 2.. 
	..statement 3.. 
 
 IF <condition> THEN 
 COMMIT; 
 ELSE 
 ROLLBACK 
 END IF; 
 
END

Stefan Helzle · Answer

Our best practice to put logic into a WTMDS node is to use expressions returning a list of entityData structures. This way you can do any checks you like and only write to DB if all your items is OK. The write to DB itself will be transactional.