PERPETUUM (VB reject function)
Although this is a very easy function, it needs some explanation what it does.
Suppose you work out a database synchronisation project where you have one master database people in the office are using, and a series of slave databases people on the road are using. People on the road are usually offline and connect themselves ones in a while to the master database to send the updates they made, and to get all updates other people made.
When you follow the trigger mechanism you understand that the TRB table only contains changes of the database you're working on. Assuming a machine is a long period offline and connects to the master machine you are always able to exchange only the changes to both database:
On the master machine you need only one connection client that handles the changes of the master database. However, on every change the master database will trigger only one line in TRB. It is up to the PRODBX connection client (and your configuration) to generate a transaction for each of the slaves.
Picture yourself the next scenario. Slave 1 has a change and generates a trigger. That trigger generates an update on the master database, and since it is a change, it generates a trigger on the master. PRODBX picks this up and sends a transaction to slave 1 and slave 2. So, slave 1 gets an update, and since it's an update, it generates a trigger, that is send to the master... got the picture. We call it the perpetuum.
The solution is two-fold:
Slave generates only triggers of his own changes
This means that any update on the slave database because of a change in another slave or the master, may not generate a trigger. This can only be solved in the slave trigger.
First you need to create an extra database table in the slave database that holds only the database id. So slave 1 would get id 1, Slave 2 would get 2... In our sample this table is called DBID and has one field 'lngdbid'
Secondly you must add to each table an extra field holding the id of the database that generated the last update of the record. Your application must support this by writing the DBID to this extra field, or work through strored procedures to get the correct DBID in this field. In our sample this field is also called lngdbid.
Adapt the trigger to generate only triggers when the DBID.lngdbid = TABLE.lngdbid. Below an example for SQL-server and insert trigger.
CREATE TRIGGER <Trigger
name> on <Table name> for INSERT AS
Master connection client may not send back a transaction
Since we added the additional field to each table holding the database id of the database generating the change, we can make it part of the source fields to retrieve when the we interpret the trigger coming from the master database. In our example the lngdbid would contain 1 if it comes from slave 1, and we must reject the output record that would go to slave 1.
As many slaves we have we will call as many times lngdbid, each time for a different record id (which is the record id for one of the slaves). We pass lngdbid through the function PERPETUUM and write it to lngdbid of the destination record. But since we enabled the 'reject function' option, it will reject the record that goes to the same database initiating the change. (In our example slave 1).
In our configuration we write the database id in the Parameter 3 field. For slave 1, parameter 3 would be 1. In the function it is compared with lngdbid and if lngdbid=1 then the record is rejected and slave 1 would not get an update back of his own change.
Remark: there are possible variants to this mechanism by using the 'TRB field to STACK' option in the connection setup.
PROMES BVBA - Laarsebeekdreef 14 2900 Schoten Belgium
Tel: +32 475 87 69 38 - E-mail: firstname.lastname@example.org