Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    14

    Unanswered: Inserting into another database table via triger?

    Is it possible to write a trigger which will insert a record into a table which resides in another database instance? Provided a database link is created and active?

    If it is possible, how would the insert statement look like?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Insert Into Table2@remote ....
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Posts
    14
    The statement worked. But what if the target database is down and the trigger can't insert into that database table, how do i detect the error and cancel the whole insert statement or write a log file?
    Last edited by vyruzj; 05-05-04 at 00:03.

  4. #4
    Join Date
    Jan 2004
    Posts
    14
    -
    -
    - re-post

  5. #5
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Quote Originally Posted by vyruzj
    The statement worked. But what if the target database is down and the trigger can't insert into that database table, how do i detect the error and cancel the whole insert statement or write a log file?
    Write a procedure that uses autonomous transaction and call it from the exception section of the trigger.
    My way or the highway. Yeah

  6. #6
    Join Date
    Jan 2004
    Posts
    14
    Meaning to say i should write a trigger that will call a procedure? BTW what is autonomous transaction?

    BTW i have tried writing a trigger but it wouldn't compile.

    CREATE OR REPLACE TRIGGER "XXX"."TESTING" AFTER
    INSERT ON "XXX"."YYY" FOR EACH ROW begin
    if :new.code='08' then
    insert into Testing@pdk.US.ORACLE.COM values('JEFF','1');
    exception
    when others then
    null;
    end if;

  7. #7
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Write a procedure to insert records in a remote table that is called from trigger body.
    Catch the exception if insert in the remote database fails.
    Handle THAT exception by calling a procedure that will log your error.
    You probably want to insert in a local table whatever you failed to insert in a remote table
    Reraise or ignore.

    Create a procedure and run it periodicaly from a batch process that will attempt to insert rows from local table of failed inserts into a remote table. Clear local table.

    You may not necessarily need to use the autonomous transaction unless you want to rollback your insert and commit the log entry.


    Read here about triggers:
    http://download-west.oracle.com/docs...g13trg.htm#431


    Read here about autonomous transactions:
    http://download-west.oracle.com/docs..._ora.htm#27440
    Last edited by Nocopy; 05-05-04 at 00:41.
    My way or the highway. Yeah

Posting Permissions

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