Insert record to non-Oracle database(MSSQL) from Trigger - Error ORA-02047
556874Jan 19 2007 — edited Jun 7 2007hi all,
There are two databases Oracle 10.1.0g and MS-SQL Server 2000. The goal is making almost on-line synchronisation between these two databases. If you have any suggestion how to this I will be greatly appreciated.
My approach and problem:
On Oracle side I made Oracle Generic Connectivity in order to link Ms Sql Server 2000 database to Oracle. I have used Sql server ODBC driver and Oracle Heterogeneous Services. I made this succesfully.
Then I have used the SQLPlus Worksheet tool and typed the INSERT SQL syntax in order to add new record to linked database (MSSQL). It was working:-)))
But the problem comes when I make a trigger on Oracle table (trigger AFTER INSERT) which triggers the INSERT SQL syntax to linked database. The INSERT syntax inside the trigger is totally the same as this one which I typed in SQLPlus Worksheet, but it doesn't work from the trigger. Why!?
The Trigger code is
create or replace trigger trig1
after insert on emp referencing new as newrow
for each row
begin
insert into emp@mssql values(:newrow.name, :newrow.empid);
end trigger;
The following errors are generated:
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "SCOTT.TRIG1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRIG1'
Anyone knows what could be wrong? Please suggest me how to solve this!!.
Thanks & Regards
Ashok.V