OnAfterUpdate/Insert/Delete triggers and business logic

Questions, tips and tricks and techniques for scripting in Servoy

OnAfterUpdate/Insert/Delete triggers and business logic

Postby udiluca » Wed Feb 10, 2016 6:54 pm

Hello,

I'm using dbEvents to update the status of some related records based on the main record status. Basically, when the option in the main record is disabled, I want this option to be disabled on some related records, on another table on another server. I'm currently using this kind of structured event in many place in our solutions ( new client folder created on the server when a new client is inserted, etc )

Actually, this is done through the onAfterRecordUpdate(), when the client status is changed and saved calling the onRecordUpdate() trigger, which purpose, in my mind was purely and strictly to validate the records changes ( "can we set this client option to "No", based on some business logic ).
But when an error happens in the onRecordAfterUpdate() - record locking or any other error occuring during one save process in the related set - there's no way to rollback the transaction to revert the main record option to what it was, even though everything started with a startTransaction(). The onRecordAfterUpdate() has a try/catch/finally structure and the errors are correctly reported back to the user so that he knows that something went wrong, but then he still has related records with some invalid status.

I saw in another thread that I can now do this inside the onRecordInsert() trigger, which for sure does the job, from several tests I did, as the main record itself is not saved if any error occurred during the related records updates.

I started thinking of changing my logic, but then, I'm wondering what is finally the purpose of the onRecordAfterUpdate/Insert/Delete if everything can now be done during the onRecordInsert/onRecordUpdate process.

As a matter of fact, some tables are shared by several applications with some separate business logic, we handle several onRecordInsert/onRecordUpdate in separate scopes, each being called to control their respective business rules ( ex : trigger in scopeA controls that client data is in synch with our servoy CRM, trigger in scopeB controls that the client parameters are valid for our Content Management solution, and trigger in scopeC checks that the options are valid in the servoy Project Management application he is using as a SaaS service.
My onAfterRecordInsert/Update/Delete are therefore called only when all on RecordInsert/Update/Delete have been succesfully triggered. As this is currently organized, I'm a bit lost if triggering these "after" logics in the "before" process.

Any idea ?

Thanks

Ugo
udiluca
 
Posts: 23
Joined: Wed Apr 10, 2013 7:25 pm

Re: OnAfterUpdate/Insert/Delete triggers and business logic

Postby swingman » Thu Feb 11, 2016 1:26 am

Hi Ugo,

Not sure if the following would work for you:

I use table-events to create records in a queue table. The queue contains a reference to the record which need to be processed. A batch processor checks the queue every minute or two and pushes or pulls updates between our 3 different PostgreSQL databases. The records to be processed are re-read before processing, so an error will cause the original saved record to be processed - possibly eliminating your problem of broadcasting a change which fails to save. The update to the remote systems are delayed by a minute or two, but in our case this is acceptable.

Also, If a remote database is unavailable, the queue simply builds up until it becomes available again.
Christian Batchelor
Certified Servoy Developer
Batchelor Associates Ltd, London, UK
http://www.batchelorassociates.co.uk

http://www.postgresql.org - The world's most advanced open source database.
User avatar
swingman
 
Posts: 1173
Joined: Wed Oct 01, 2003 10:20 am
Location: London

Re: OnAfterUpdate/Insert/Delete triggers and business logic

Postby udiluca » Thu Feb 11, 2016 5:16 pm

Hi Christian,

Actually this was the way I started thinking of it before going into the trigger way. It could be a complementary system, that's true.

In order to prevent the user to be presented error messages coming from these triggers method, we call the same record checking logic that the trigger goes through in the UI, and any error catched during the validation process ( pre/post ) is stored into a variable so that we know that something went wrong. In most of the case, we are able to revert the record going through the changes dataSet that was stored, but in some cases, it's simply impossible ( or too complex ) because part of the process has been successfully completed and other triggers on other tables may have been called in cascade.

When doing it from our back-office, where the UI is poor and any column from each table can be manipulated through table views forms, along with methods dedicated to some longer process, it's even worse. Triggers should guarantees the overall integrity of our system, preventing errors to be made during incorrect data manipulations, but then even if all these table views have AutoSave OFF, and any save is done with StartTransaction(), there's no way to revert the record based on the error captured during an AfterRecordUpdate() and rollback the transaction then.

BTW what exactly happens and what is the purpose of the "revertSavedRecords" from the databaseManager.rollbackTransaction() ?

Code: Select all
databaseManager.startTransaction()
databaseManager.saveData(record)
if(!databaseManager.saveData(record)){
//call method A that returns a message with the error thrown
return
}
else{
//check that errors were posted by any afterRecordInsert/Update
if(globals.logError!=null){
databaseManager.rollBackTransaction(true, true);
//explain what happened in a dialog
}
}
udiluca
 
Posts: 23
Joined: Wed Apr 10, 2013 7:25 pm


Return to Methods

Who is online

Users browsing this forum: Bing [Bot] and 3 guests