SQL & PL/SQL

Hi, I hope that somebody might be able to advise me about how I can solve this problem with my AFTER INSERT trigger.

I tend not to use triggers but I thought this one could end up as a convenient solution to my bigger problem, if only I could get it to work. The main problem is that I need to update a second database (DB2) with a row that includes a CLOB each time a new row is written to my first database DB1. This is not by design, it is due to the fact that we need to have a second system running off an original commercial system.

I noticed that, although I cannot pull the CLOB into DB2 from DB1 using a SELECT statement via a DB link, it is possible to "push" the CLOB into DB2 using an UPDATE statement from DB1 via a DB link. I still don't understand /why/ this is, but I thought I could use a trigger to take advantage of this.

There is a table ORIG_TABLE in DB1 with columns DB1_ID (NUMBER), CLOB1_COLUMN (CLOB) and there is a table TEST_CLOB in DB2 with columns DB2_ID (NUMBER), CLOB2_COLUMN (CLOB).

So I set up a trigger in DB1 as below:

create or replace trigger insert_clob
after insert on orig_table
for each row
begin

     -- Store newly created ID in DB2 table
     insert into test_clob@db2_link
     (db2_id)
     values(:new.db1_id);

     -- Then send over CLOB using UPDATE
     update test_clob@db2_link
     set clob2_column = (select clob1_column from orig_table where db1_id = :new.db1_id)
     where db2_id = :new.db1_id;

end;

The two statements work OK from SQL*Plus when run individually. When I attempt to combine them into a trigger I get an "ORA-02055 distributed update operation failed; rollback required" error and an "ORA-04091 table orig_table is mutating" error.

Firstly, I am uncomfortable having to add the FOR EACH ROW clause just to get the trigger to compile. Secondly, does anybody have an alternative solution as to how I can pass a CLOB to a second database via a trigger?