Re: Commit on DB-link

Alex Vilner wrote:

> Hello!
>
> We have 2 database instances, with data being moved from one into
> another through the use of triggers, operating on DB-link (Primary
> server has DB-link connection to Secondary. Tables on the Primary
> server have triggers that insert/update data into Secondary). Simple,
> straightforward scenario :)
>
> Here is one issue, though: when the update or insert fails from the
> Primary to the Secondary, we want the transaction to roll back, but to
> have an entry created in the table on the primary server, which serves
> as the log. Now, since these are the triggers that do the data moves,
> I am not sure we can use COMMIT and ROLLBACK, to commit the data to
> the log, while rolling back the actual transaction...
>
> What can you suggest as a way of dealing with this situation? Oracle
> Queue has been suggested, but this makes it not "real-time" anymore --
> enqueueing and dequeueing is obviously delayed, and is dependent on
> the queue fill-up and general availability.
>
> Thank you in advance!
>
> --Alex

You can not do commit in triggers. But you can write a procedure that is an autonomous transaction that is called by the exception block and contains a commit.

Daniel Morgan Received on Mon Jun 17 2002 - 11:31:02 CDT