| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Commit on DB-link
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
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...Received on Mon Jun 17 2002 - 13:27:12 CDT
> 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
>
 |
 |