Home » RDBMS Server » Networking and Gateways » can't insert data to SQL server within an oracle trigger
can't insert data to SQL server within an oracle trigger [message #245002] Thu, 14 June 2007 19:53 Go to next message
HC@ZZF
Messages: 2
Registered: June 2007
Junior Member
1) my simple trigger in oracle side
create or replace
TRIGGER TESTRI
AFTER INSERT ON TEST1
FOR EACH ROW
BEGIN
insert into test1@sqlserver (city,state) values ('what','nine');

END;

2)invoke trigger in oracel side.

SQL> insert into test1 values ('damn','da');
insert into test1 values ('damn','da')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "GGWEB.TESTRI", line 3
ORA-04088: error during execution of trigger 'GGWEB.TESTRI'

3) if insert date without inside a trigger, it works well

SQL> insert into test1@sqlserver (city,state) values ('what','nine');

1 row created.

SQL> commit;

Commit complete.


SQL> select * from test1@sqlserver;

CITY STATE
------------------------------ ----------
Fremont CA
Hayward CA
San Jose PA
what nine

SQL>
Re: can't insert data to SQL server within an oracle trigger [message #245003 is a reply to message #245002] Thu, 14 June 2007 21:17 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
02047, 00000, "cannot join the distributed transaction in progress"
// *Cause: Either a transaction is in progress against a remote database
//         that does not fully support two phase commit, and an update
//         is attempted on another database, or updates are pending and
//         and an attempt is made to update a different database that
//         does not fully support two phase commit.
// *Action: complete the current transaction and then resubmit the
//          update request.
Re: can't insert data to SQL server within an oracle trigger [message #245230 is a reply to message #245002] Fri, 15 June 2007 10:47 Go to previous messageGo to next message
HC@ZZF
Messages: 2
Registered: June 2007
Junior Member
I know the reason, but how to fix it? Thanks!-Henry
Re: can't insert data to SQL server within an oracle trigger [message #245240 is a reply to message #245002] Fri, 15 June 2007 11:17 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
What happens if between the following 2 actions
>2)invoke trigger in oracel side.
>SQL> insert into test1 values ('damn','da');
you do the following instead?
2)invoke trigger in oracel side.
COMMIT;
SQL> insert into test1 values ('damn','da');

Previous Topic: ORA-12154: TNS:could not resolve service name.
Next Topic: Accessing Oracle from Excel Outside the Firewall
Goto Forum:
  


Current Time: Fri Dec 02 23:14:59 CST 2016

Total time taken to generate the page: 0.12066 seconds