DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Replication using triggers. Urgent!

  1. #1
    Join Date
    Dec 2002
    Location
    Mumbai
    Posts
    7

    Replication using triggers. Urgent!

    Hi all,

    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!.

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 .

  3. #3
    Join Date
    Dec 2002
    Location
    Mumbai
    Posts
    7
    Hi, Both databases are the same (Oracle8i 8.1.6.0.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
    end if

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width