Insert using DBLink; Trigger is not executing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Insert using DBLink; Trigger is not executing

Hybrid View

  1. #1
    Join Date
    Apr 2007
    Posts
    2

    Insert using DBLink; Trigger is not executing

    Hi All,

    I have a procedure [on DB-1], where in i am using DBLink to insert the data to a table on DB-2. and i have trigger on the same table of DB2.

    Inserting the data from DB-1 to TABLE.DB-2 is working fine, but the trigger on TABLE.DB-2 is not getting executed immediately. The trigger is firing only when the 2nd record is inserted.

    Even though the records exist, trigger is not happening.

    Ex:
    insert into DB-2.TABLE values ('A',1); -- On this insert trigger shul work.
    insert into DB-2.TABLE values ('B',2); -- On this insert trigger shul work.

    But the first trigger [A,1] is happening when B&2 are there, also B&2 will trigger on insert of C&3.

    Has anyone faced the same issue, what is the fix for this.

    Any help wud be highly appreciated.

    Thanks,

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Triggers fire once per transaction.
    Behavior is consistent with having both insert statements in a single transaction.

    BTW... posting the code of your trigger and your Oracle version wouldn't hurt
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2007
    Posts
    2
    Hello,

    Thanks for your quick reply.

    In the procedure [DBA] i have a insert statement and after that; i have a commit

    INSERT INTO APPS.RSC_INTERFACE@RSC_LINK_APPS_88 (INT_ID,INT_READER_ID,INT_EPC_IDS,INT_CREATION_DATE)
    VALUES (seqVal,readerID,(tagDataOnly),SYSDATE);
    COMMIT;
    and in trigger is like

    CREATE OR REPLACE TRIGGER APPS.RSC_TRG_INTERFACE_TRANSACTION
    AFTER INSERT
    ON APPS.RSC_INTERFACE for each row --REFERENCING NEW AS NEW OLD AS OLD
    DECLARE

    .....

    Its not working, Only if the 2nd record is inserted, trigger on 1st rec is executed..

    Can you help me on this.

    Thanks,

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    It looks like trigger is fired late, isn't it?

    When 2nd row inserted... first row trigger is fired
    When 3rd row inserted... second row trigger is fired
    etc.

    In a distributed transaction applies the concept of commit-point-site. Commit-point-site is the place where the query is actually running and commit is processed there first, then distributed to the other nodes (in this case the place where you want to insert the data).

    I've noticed your query is pushing the data over the DBLink.

    I'm wondering if there is any chance of pulling the data over the DBLink meaning, running your process on the target database and reading data from the source one. This way the Target database will be your commit-point-site, commit will be executed there first and (hopefully) trigger will be fired at the time you expect it to be fired.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Feb 2005
    Posts
    158
    A slight amendment to the previous comment.
    A DML trigger fires either once per row or once per statement. This one is defined as FOR EACH ROW in this case.
    However the effect of the trigger will generally only become apparent (to any other session) after the commit. As indicated, it is probably due to the way the commit is propogated that the trigger does not APPEAR to have fired.
    If you put something not transactional in the trigger, you could verify this. For example a call to dbms_application_info.set_client_info should become visible in the client_info column of v$session (for the session on the remote db) immediately the trigger fires, without depending on the commit.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Sharinagu,

    What's your oracle release?

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