Discussion
Data intigrity during multiple RDB-Save
Hi,
I have a situation where I have to insert data into two tables using RDB-Save. The issue is if second insert fails then the first insert should also be reverted. Basically I don't want to insert data in the first table if the second one fails as it happens during two phase commit. There is a Primary- Foreign Key relation in the tables. Is it possible to achieve this using RDB since RDB commits automatically?
Thanks in advance to all the great minds!
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
Two or more approaches can be used -
a)Before inserting, check to see if you have a record in the tables with the same key ?
b) convert your operation into a store proc
https://pdn.pega.com/forums/prpc/pegarules-database/rollback-during-rdb-saves
"Enter an SQL statement to be executed by an activity that includes the RDB-Save method.
As a best practice, include only one SQL statement on this tab. If your situation requires multiple statements, place one statement each in separate Connect SQL rules. Your activity can cause the statements to be executed in the desired sequence. If your situation requires many statements executed in sequence, consider using a stored procedure. (Pega 7 Platform uses prepared statements and submits the SQL code in a single call to database software. Some database software versions may allow multiple statements in one call, but other software versions do not; use of multiple statements on this tab reduces portability and flexibility.)"
https://pdn.pega.com/sites/pdn.pega.com/files/help_v719/procomhelpmain.htm#rule-/rule-connect-/rule-connect-sql/save.htm
c) If second insert failes, remove the insert performed by your first query.
I prefer store proc personally.