Question
Cognizant Technology Solutions
GB
Last activity: 18 Nov 2020 18:27 EST
Database triggers have executed but obj-save has failed from a service context
Hi,
We have multiple obj-save in a service activity without write Now option selected, this saves the data in multiple database tables. Each table has an associated DB trigger.
After the service is fired, data is saved and the database trigger gets fired which further updates the record, i.e there are two updates one from Pega end and another at database end through the DB triggers.
There is a final commit at the end of the service activity. If say, the 5th Obj-save operation fails in the service activity causing all transactions to rollback(as there were no commits in between), should we still see the effect of trigger on the database table for any of the earlier obj-save operations?
Could someone please help with this question. We are facing this issue in Production environment where the database triggers have fired but transaction has rolled back.
Pegasystems Inc.
FR
Hello,
Is it really specific to Production or can you force a replication on lower environment.
If the transaction has rolledback I don't think the database trigger should fire
Cognizant Technology Solutions
GB
Hi, sorry for the late reply. I could not replicate the issue in lower environment. Also, it seemed to be a one-off issue in Production and we monitored the cases for a couple of weeks and couldn't see another instance of the above issue.
However, am still unable to find the root cause of the above issue.
Pegasystems Inc.
AU
It's been decades since I wrote a database trigger but my memory on ORACLE was that your event options where "BEFORE" or "AFTER" UPDATE, without me appreciating at the time what the difference was. Perhaps the subtle distinction plays a part here if similar options are available on your DB platform.
From a Pega point of view you would in more control - and be more upgrade-proof - if the logic in your DB trigger can be implemented in Pega (as a Declare Trigger), be subject to Pega's transaction management, and be traceable, loggable, etc.
In principle I agree with @MarcLasserre_GCS ... if Pega never issues the Commit (if your Activity logic catches the failure and issues a Rollback), then none of your deferred saves get sent to the database and there's no database events to fire the DB triggers.
Make sure though you are explicitly issuing a Rollback or Obj-Save-Cancel if that's your intent. If your logic just "skips" the Commit, the earlier deferred saves are still queued to be sent to the database what that Requestor next reaches a Commit. This could be from a separate Thread or Interaction. That's unlikely in a Service Activity where the Requestor likely lapses at the end of the service call, but watch out for that.