Replication using triggers. Urgent!
I have two tables(emp) in 2 different databases(say A and B). I want to keep these 2 tables perfect copies of each other (any insert/update/delete on table shud be reflected on the other side and vice versa). I have written triggers on both the tables to achieve this and in order to prevent them from recursively firing i have made use of a package with 2 variables. Now this package is in database A and in each trigger i use the 2 variables in the package and appropriately issue insert/update/delete commands to the other side.
Now this logic works fine when the 2 tables are in the same database, but when i use database links to achieve this then surprisingly insert/update/delete works fine from B to A .From A to B insert and delete works but while updating i get the following error :
ERROR at line 1:
ORA-02055: distributed update operation failed; rollback required
ORA-08006: specified row no longer exists
can somebody please help me out here!.
what version are u using in source database and target database ?
could u post ur update statement ?
looks that u are hitting some bug . need to patch ur database .
check for any other errors in the alert log or trace files generated .
Hi, Both databases are the same (Oracle8i 220.127.116.11.0)
actually my logic is something like this
In database A i have one table emp and also a package rep_pack which has two flags which i check in the respective triggers. Now in both database A and database B i have table emp. I have written triggers on both tables which access the variables in rep_pack. the pseudocode for the trigger is something like this
if not flag1 then
set flag2 = true
insert/update/delete table across the link
set flag2 = false
Now the trigger in database B uses a procedure to set the flag because it cannot directly access package variables across the link.
I am not very clear but I think this is the problem maybe because package variable value is not retained across sessions, but surprisingly i dont understand how any ins/upd/del done at B is reflected at A but any upd at A causes a problem.
Anyway error was solved by using 2 packages in both databases with a single variable in them and using procedures to set them and now it works fine for all scenarios
Click Here to Expand Forum to Full Width