Update where procedures
As explained in the UpdateWhere documentation it is possible to apply a transformation to some rows of a store by applying a procedure once during a transaction. However, there are some cases where we want to apply a transformation recurrently, for all rows inserted or updated in the store when they match a given condition. For that, it is possible to register the update where procedure to a store.
There are 2 types of procedure registration: insertion-time and commit-time. While they are very similar in their usage, they slightly differ in terms of performance and behavior.
Read the how-to guide to learn how to register an updateWhere procedure.
Insertion-time update where trigger
Update-where procedures can be registered within the Datastore engine, as Insertion-time update-where triggers. The triggers are then applied to modify all records inserting to the selected store, before submitting them to the right partitions. In other words, if there is an insertion-time trigger on a store, any record inserting to this store will be modified by the registered procedure, before really being submitted to the store.
Applying an update where in a transaction on this store won't trigger this registered procedure.
This "update where at insertion" procedure should be faster than the update-where procedure run at commit time, but it comes with the limitation that it only listen to changes in the selected store.
Commit-time update where trigger
Update-where procedures can be registered within the Datastore engine, as Commit-time update-where triggers. These triggers are then applied at each transaction commit. They only impact the records that are impacted by the modifications in the current transactions.
These include:
- Records which have been inserted or updated in the store the trigger is registered on.
- Insertion and updates in the store referenced by the store of the trigger (records in this store which have a reference to this updated data will be updated).
The commit-time update-where triggers can be chained:
- Within the same store: you can define different triggers and sort them by priority. Thus, you can use the result of one trigger to perform the computation of another trigger.
- Cross-stores: at the commit() of a transaction, triggers will be applied from "leaves" stores to stores that do not have any incoming reference (no other stores have references to them). Thus, you may use in a given trigger selection a column that has been calculated by another trigger (executed previously).
Business use cases examples
- Compute a business rating on a transaction based on attributes coming from different tables (customer info, rating, ...).
- Partitioning on a field coming from a reference field (ex: partition risks on trade's counterparties).
Comparison
| Insertion-time Update-Where Triggers | Commit-time Update-Where Triggers | |
|---|---|---|
| API | Almost the same, only accepts condition on selected (base) store | Almost the same, accepts condition on the selected store and all joined stores |
| Triggered | A record is added to the selected store | Any commit that modified any of the stores in the selection |
| Execution | Before submitting record to the selected store, always in place | During the commit (after submitting all records). Trying to do in place modification if possible, otherwise, mark the record as deleted and re-submit it. |
| Consistency | Commit in joined does not trigger the procedure | The concerned columns are always up-to-date. |
| Performance and Memory | Should be better as each record is submitted only once |