Re: Triggers & Rollback

One way to do this is with an autonomous transaction. You can commit within the trigger and then raise the exception for informational purposes only. I would, however, argue that you probably shouldn't do it and that depending on what else is happening it may not work for you. Another way would be to insert a message into a global temporary table that is always read after each transaction. Inefficient but solves the problem.

Daniel Morgan

Thomas Kyte wrote:

> In article <497cda72.0204081424.2c5cb81c_at_posting.google.com>, uwda_at_yahoo.com
> says...
> >
> >I have an BEFORE INSERT trigger that, when a condition is met, I alert
> >the user with an RAISE_APPLICATION_ERROR. The problem is when I do
> >that, an automatic rollback happens.
> >
> >What I'm trying to accomplish is alert the user that something is
> >amiss, but then fix it for them. Any ideas?
> >
> >
> > CREATE OR REPLACE TRIGGER trg_foo_bar
> > before Insert
> > On table_1
> > For Each Row
> >
> > Begin
> > if :new.hNumber <> 10 then
> > raise_application_error(-20101, 'Value not 10!');
> > :new.hNumber := 10;
> > end if;
> > End;
> >
> >Thanks
> >
> >J.
>
> That is just not going to happen.
>
> What I could envision is something like this;
>
> create or replace package warnings
> as
> type myArray is table of varchar2(255) index by binary_integer;
>
> messages myArray;
> end;
> /
>
> create or replace trigger BI_trigger
> before insert on T
> declare
> empty warnings.myArray;
> begin
> warnings.messages := empty;
> end;
> /
>
> CREATE OR REPLACE TRIGGER trg_foo_bar
> before Insert
> On table_1
> For Each Row
>
> Begin
> if :new.hNumber <> 10 then
> warnings.messages( warnings.messages.count+1 ) := 'wooops, not 10';
> :new.hNumber := 10;
> end if;
> End;
>
> and have the client LOOK for messages afterwards. Anything that flings an error
> back it by design going to rollback the offending statement (it caused an ERROR
> after all!)
>
> Seems like the client shouldn't even be putting in a value for this column -- it
> should just default and not even be inserted into...
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Tue Apr 09 2002 - 10:08:10 CDT