where do i find samples that illustrates the use of the SQL Remote hook stored procedures? - how to process/intercept new changes (updates, inserts, deletes) - how to prevent/skip certain updates to the target database
I wasn't able to find any sample code. Thanks a lot Frank
It seems there are no (or few?) public samples available, which is a pity.
Nevertheless, I'd ask first what are you trying to achieve?
Basically, a hook procedure is just a procedure with a pre-defined name which is then called by SQL Remote - think of a callback.
What we are using such hook proedures for is basically one thing:
That has to do with the following SQL Remote rule:
Here's a small sample:
Say, you have remotes that generate some kind of orders, and you want those orders to have a central order number or a central registration time once the order has been applied within the consolidated database. Until the order is applied at the cons, it would just have a provisory order number marked via a field OrderNrIsProvisory.
So you might have a trigger in the cons that runs when a new order is processed and would do an update on the order to make it a "central one", such as
... update T_Order set OrderNr = <...>, OrderNrIsProvisory = 0 where OrderNrIsProvisory = 1; ...
Straightforward, but it won't work as expected within SQL Remote:
If that trigger is run within the contexts of DBREMOTE, it will not sent that UPDATE back to the remote the order was created, since DBREMOTE does not do that unless an update conflict was detected (see the cited ruel). As a consequence, the remote won't have the freshly updated values.
One solution here is to use the trigger for a different purpose: Do not change the row itself but do insert an entry into a help table (say T_NewOrder) that only exists on the cons (and therefore won't be replicated at all).
Then, use a hook procedure like sp_hook_dbremote_receive_end() to process that help table, such as
create procedure sp_hook_dbremote_receive_end() begin for forNewOrder as crsNewOrder cursor for select OrderID as NewOrderID from T_NewOrder order by NewOrderID for update do update T_Order set OrderNr = <...>, OrderNrIsProvisory = 0 where OrderNr = NewOrderID and OrderNrIsProvisory = 1; delete where current of crsNewOrder; end for; end;
Note, DML actions run within one of the SQL Remote hooks run as normal code, i.e. they don't fall under the "Do not replicate back to the originator" rule. As such, the according UPDATE will be replicated to all according remotes, just as it would have been done in a normal SQL session within the remote.
That's because procedures specified in a hook are called on a separate connection.