Re: Commit on DB-link

"Vladimir M. Zakharychev" wrote:

> Well, what you actually said is 'you can commit in triggers using
> autonomous transactions'. :) Having separate logging procedure
> with autonomous transaction is more practical, especially if you
> only want to log errors, which are expected to be rare, but you still
> can commit right from the trigger itself if it is declared with autonomous
> transaction pragma.
>
> A practical solution might look like this:
>
> create table log_table(
> user_name varchar2(32),
> err_date date,
> err_msg varchar2(2000)
> )
> /
>
> create or replace procedure log_error(err_msg varchar2)
> as
> PRAGMA AUTONOMOUS_TRANSACTION;
> begin
> insert into log_table values (USER, sysdate, substr(err_msg,1,2000));
> COMMIT;
> end;
> /
>
> create or replace trigger some_trigger
> <before|after>whatever on some_table
> <for each row>
> begin
> update some_other_table_at_dblink set some_column = :NEW.some_column
> where <your conditions>;
> exception
> when others then
> -- although this procedure commits, it is safe to call it from trigger because
> -- it runs in an autonomous transaction and commits only its own work
> log_error('Failed to update some_table.some_column with '||:NEW.some_column);
> raise; -- re-raise the error
> end;
> /
>
> This will trap the error in trigger, log it in an autonomous transaction
> and re-raise the error so that it can be caught up the call chain and the
> triggering transaction can be rolled back.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D0E0EB8.8D5D16A9_at_exesolutions.com...
> > 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
> >

Well I actually didn't say it as I didn't want to write too complex a response ... but you are, of course, correct.

Daniel Morgan Received on Mon Jun 17 2002 - 14:33:13 CDT