Re: Triggers & Rollback

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 8 Apr 2002 18:01:52 -0700
Message-ID: <a8tei001beb@drn.newsguy.com>

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 Mon Apr 08 2002 - 20:01:52 CDT