Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unhappy Unanswered: recursive triggers ?

    I have two tables (T) with same structure, belonging to different users(U1,U2), but also one 'higher' user (U) which have access rights on both.

    I would like these two tables to be synhronized, ie. after inserting new row in one table, oracle should copy contents of new row into the second table, and vice versa.

    Obviously, I created two triggers both belonging to user U:

    CREATE OR REPLACE TRIGGER U.U1_INS_UPD
    AFTER INSERT OR UPDATE ON U1.T
    FOR EACH ROW
    DECLARE
    ...
    BEGIN
    ...
    END;


    CREATE OR REPLACE TRIGGER U.U2_INS_UPD
    AFTER INSERT OR UPDATE ON U2.T
    FOR EACH ROW
    DECLARE
    ...
    BEGIN
    ...
    END;


    Unfortunately, I have here recursive trigger activation, so I tried to disable trigger U2_INS_UPD when executing U1_INS_UPD, and vice versa, but compilation error occured.

    I tried to put following sentence as the first command in the trigger body, just after BEGIN:

    ALTER TRIGGER U2_INS_UPD DISABLE;

    Is that correct. Please advice. Thank you in advance...

    Regards, Vladimir.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have two tables (T) with same structure, belonging to different users
    >(U1,U2), but also one 'higher' user (U) which have access rights on both.
    >I would like these two tables to be synhronized, ie. after inserting new
    >row in one table, oracle should copy contents of new row into the >second table, and vice versa.

    All of this begs the question.........
    Then, why have TWO tables to begin with?????????????????????
    Would not life be MUCH simpler with only a single table?
    Why do you REALLY "require" to have two tables?

    Unless of course, this is just your typical homework assignment.

  3. #3
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: recursive triggers ?

    Originally posted by vbugarsk
    I have two tables (T) with same structure, belonging to different users(U1,U2), but also one 'higher' user (U) which have access rights on both.

    I would like these two tables to be synhronized, ie. after inserting new row in one table, oracle should copy contents of new row into the second table, and vice versa.

    Hi, It is not possible with the triggers. But it is accomplished by the use of
    (1) Oracle Streams (for 9.2.0.2 or higher) Or

    (2) creating jobs and using DBMS_JOB

    In this case, inserts on T1 will not be sent to T2 (and vice a versa) at the same time as it is done with the triggers, Instead, You have to have some time difference between these, say, 10 seconds.

    For more Information, Please read the Oracle Documents.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  4. #4
    Join Date
    Dec 2003
    Posts
    3
    i do'not know?

  5. #5
    Join Date
    Dec 2003
    Posts
    4
    >All of this begs the question.........
    >Then, why have TWO tables to begin with?????????????????????
    >Would not life be MUCH simpler with only a single table?
    >Why do you REALLY "require" to have two tables?

    >Unless of course, this is just your typical homework assignment.[/SIZE]

    Thank you for the reply. This is not homework. It is actually specific CAD/CAM naval architecture application (Foran) which is creating Oracle DB tables and manipulating with the data. I can not change the application. It creates separate user(schema) for each ship we are modelling, and around 500 tables for each vessel. On two of these vessels it is very important to keep one table synhronized, so I came to idea to try to define in the Oracle directly automatic mehanism for keeping this sinhronicity.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Whose dumb idea was it to have a separate schema per ship?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    One possible kludge workaround would be to simply DROP the table in question from one of the schemas and then create a VIEW of the same name which references the corresponding table in the other schema.
    After issuing appropriate GRANTs to the schema which now has the VIEW ideally everything else in the application would never know anything had changed.

    HTH & YMMV

  8. #8
    Join Date
    Dec 2003
    Location
    Wuhan,China
    Posts
    4

    Re: recursive triggers ?

    tell us the table structure,
    is there primary key in your table ?
    i think you can creat two triggers both belonging to user U:

    CREATE OR REPLACE TRIGGER U.U1_INS_UPD
    AFTER INSERT OR UPDATE ON U1.T
    FOR EACH ROW
    DECLARE
    cnt integer;
    ...
    BEGIN
    ...select count(*) into cnt from table1
    where column1=:new.value1
    and column2=:new.value2
    and column3=:new.value3
    and columnN=:new.valueN;
    if cnt=1 then
    --donothing
    else
    --insert or update table2 as you like
    end if
    END;

    trigger2 is the same to trigger1
    if there is primary key in table1 ,then you can use this SQL:
    select count(*) from table1
    where the primary column=:new.value;
    it is the safe way,because the record is unque.

    my english is poor,i hope you will know my idea

  9. #9
    Join Date
    Dec 2003
    Posts
    4
    Thank you for all answers.

    Replacing second table with view might be good idea, but unfortunatelly, primary keys for these two tables are not the same, and application itself insert new primary keys, not using oracle sequences, therefore everything in this table is the same, except primary keys. In the "copy" table, I put this primary key as MAX(OID)+1.

    Laiyito_cn: Your idea is also very good. I have one column (NAME) which is unique and should be same in both tables (not primary keys itself), so I rearrange your idea to be like this (body of the trigger U.U1_INS_UPD):

    DECLARE
    newid number;
    cnt integer;

    BEGIN
    IF INSERTING THEN
    SELECT COUNT(*) INTO cnt FROM U2.T WHERE NAME=:new.NAME;
    IF cnt=0 THEN
    SELECT MAX(oid)+1 INTO newid FROM U2.T;
    INSERT INTO U2.T VALUES
    (newid,
    :new.SECTION_TYPE,
    :new.NAME,
    :new.SYMMETRY,
    :new.PASSING,
    :new.AFTER_FRAME,
    :new.DIST_AFTER_FRAME,
    :new.FORE_FRAME,
    ...........)
    END IF;

    ELSIF UPDATING
    ......
    ENDIF;
    END;

    This compiled without errors, but after I tried to insert, I've got following message:

    ORA-04091: table U1.T is mutating, trigger/function may not see it.
    ORA-04088: error during execution of trigger U.U2_INS_UPD
    ORA-04088: error during execution of trigger U.U1_INS_UPD

    It becomes complicated, ha...

    By the way, your English is very good.

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try trapping the error ... ie;

    IF INSERTING THEN
    begin
    SELECT COUNT(*) INTO cnt FROM U2.T WHERE NAME=:new.NAME;
    exception
    when NO_DATA_FOUND THEN
    SELECT MAX(oid)+1 INTO newid FROM U2.T;
    INSERT INTO U2.T VALUES
    ....
    when others then
    return;
    end;

    hth
    Gregg

  11. #11
    Join Date
    Dec 2003
    Location
    Wuhan,China
    Posts
    4
    Originally posted by vbugarsk
    Thank you for all answers.

    Replacing second table with view might be good idea, but unfortunatelly, primary keys for these two tables are not the same, and application itself insert new primary keys, not using oracle sequences, therefore everything in this table is the same, except primary keys. In the "copy" table, I put this primary key as MAX(OID)+1.

    Laiyito_cn: Your idea is also very good. I have one column (NAME) which is unique and should be same in both tables (not primary keys itself), so I rearrange your idea to be like this (body of the trigger U.U1_INS_UPD):

    DECLARE
    newid number;
    cnt integer;

    BEGIN
    IF INSERTING THEN
    SELECT COUNT(*) INTO cnt FROM U2.T WHERE NAME=:new.NAME;
    IF cnt=0 THEN
    SELECT MAX(oid)+1 INTO newid FROM U2.T;
    INSERT INTO U2.T VALUES
    (newid,
    :new.SECTION_TYPE,
    :new.NAME,
    :new.SYMMETRY,
    :new.PASSING,
    :new.AFTER_FRAME,
    :new.DIST_AFTER_FRAME,
    :new.FORE_FRAME,
    ...........)
    END IF;

    ELSIF UPDATING
    ......
    ENDIF;
    END;

    This compiled without errors, but after I tried to insert, I've got following message:

    ORA-04091: table U1.T is mutating, trigger/function may not see it.
    ORA-04088: error during execution of trigger U.U2_INS_UPD
    ORA-04088: error during execution of trigger U.U1_INS_UPD

    It becomes complicated, ha...

    By the way, your English is very good.

    sorry, if no_records_found the cnt is null,so we can't use cnt=o.
    gbrabham's idea is very good,but if you don't want use exceptions,
    you can uses these:
    IF nvl(cnt,0)=0 THEN

  12. #12
    Join Date
    Oct 2003
    Posts
    706
    Originally posted by vbugarsk
    [...]I can not change the application. It creates separate user(schema) for each ship we are modelling, and around 500 tables for each vessel. On two of these vessels it is very important to keep one table synhronized, so I came to idea to try to define in the Oracle directly automatic mechanism for keeping this synchronicity.
    I believe that you're going to need to do this by means of creating some kind of log-table that records the record-IDs and tables that are "now out-of-sync in this database with respect to the other." And then have a separate, periodic process that reads this log, selects the records and updates them. Triggers could be used to create these log-entries ... obviously there will be the problem that these updates will create more log-entries that will have to be recognized and discarded. It will be extremely tough to provide this in the context of simultaneous multi-user access to the same DBs, so I don't claim to be providing a complete solution here, but I think that this is "generally the way you'll need to go."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    He says 'one needs to match the other' but then he says vice-versa.
    Very confusing, but at least this is expected from the Navy.

    Can't you use IF table.NAME%FOUND THEN
    or something?

    How much real-time updating is needed?
    What if you updated every hour, 30 min, 15 min or something?
    Is that acceptable?

    Then you could write a proc and call it every interval
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    I am agree with the duck and thats what I mentioned in my previous post regarding the same question.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

Posting Permissions

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