触发器操作异构数据库

[ylczy]

功能需求:
往oracle数据库A表插入一条记录的往SQLSERVER数据库表B插入记录.通过A表的触发器实现. 应用oracle的DATALINK与sqlserver异构连接已经成功.

通过SQL语句直接往SQLSERVER数据库B表插入数据,都很正常.
insert into bj_ysd_remote@dblink remote
value(:new.x1,:new.x2,……)

问题:把上述语句写入A表的触发器中,再往A表插入记录可以执行成功,但是没有往SQLSERVER表Ｂ插入记录，再对表Ａ操作时会报错："ora-01591 锁定已被有问题的分配事物处理4.28.20989挂起"

[tgm78]

ORA-01591 lock held by in-doubt distributed transaction string

Cause: An attempt was made to access resource that is locked by a dead two-phase commit transaction that is in prepared state.

Action: The database administrator should query the PENDING_TRANS$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, the database administrator should contact the database administrator at the commit point if known or the end user for correct outcome, or use heuristic default if given to issue a heuristic COMMIT or ABORT command to finalize the local portion of the distributed transaction.


检查是否commit了，必要的时候看看db_link是否通畅
select sysdate from dual@db_link；

[ylczy]

db_link肯定是通畅的，因为直接执行ＳＱＬ语句(select\del\insert)都是可以的。

触发器中是自动事件提交，不允许加入commit的.
我怀疑是触发器的自动事件提交没有成功，所以原始表处于被锁定挂起状态，但是原始表的数据又插入成功，没道理。　　看来还是oracel和sqlserver异构的问题没弄明白。

[tgm78]

估计是oracle端无法commit到sqlserver端所致