ron
Member
OE 11.7.4 on Linux
Hello ....
We're developing a system to extract details from database "A" using replication triggers. When the trigger executes we want to copy the details we're after to table "X" -- and write a controlling record to table "Y". So ... an "X" record will contain the data we want, and the corresponding "Y" record will only have date/time, transaction number and ROWID.
A "chaser" program will scan table "Y" and use each record to get the data in "X" and send it away.
I know that's just a simplistic overview ... lots of details under the cover.
The issue is making sure that the details copied into table "X" have actually been committed. The trigger logic will copy the transaction number and the "chaser" program will check whether the transaction is still active. If it is not active we take that to mean that the transaction is no longer active -- and therefore the update has been committed.
Now how the difficult bit ....
If all of these records are in the same database (ie, "A") then the update and the records written to "X" and "Y" are all scoped by the exact same transaction and so if for any reason the update is aborted and rolled-back the update will be undone and the records in "X" and "Y" will be removed. All Good.
But how about if tables "X" and "Y" are in a separate database "B"? The transaction scoping the activity on "X" and "Y" is local to database "B" and presumably if the original transaction is rolled-back then the records in "X" and "Y" will remain -- which is a problem.
Can anyone confirm that this is what will happen? And if it will happen -- is there a way to "fix" the problem??
Ron.
Hello ....
We're developing a system to extract details from database "A" using replication triggers. When the trigger executes we want to copy the details we're after to table "X" -- and write a controlling record to table "Y". So ... an "X" record will contain the data we want, and the corresponding "Y" record will only have date/time, transaction number and ROWID.
A "chaser" program will scan table "Y" and use each record to get the data in "X" and send it away.
I know that's just a simplistic overview ... lots of details under the cover.
The issue is making sure that the details copied into table "X" have actually been committed. The trigger logic will copy the transaction number and the "chaser" program will check whether the transaction is still active. If it is not active we take that to mean that the transaction is no longer active -- and therefore the update has been committed.
Now how the difficult bit ....
If all of these records are in the same database (ie, "A") then the update and the records written to "X" and "Y" are all scoped by the exact same transaction and so if for any reason the update is aborted and rolled-back the update will be undone and the records in "X" and "Y" will be removed. All Good.
But how about if tables "X" and "Y" are in a separate database "B"? The transaction scoping the activity on "X" and "Y" is local to database "B" and presumably if the original transaction is rolled-back then the records in "X" and "Y" will remain -- which is a problem.
Can anyone confirm that this is what will happen? And if it will happen -- is there a way to "fix" the problem??
Ron.