| 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: Triggers & Rollback
I'm with Daniel here. It seems to me that good design is to do one and only
one of the following
if you do both you must run the risk of the user rerunning the transaction with corrected values after you have also corrected them. Or of raising an error message that says 'there was an error but I fixed it so even though this is an error message just ignore it'. This type of error message does not help users.
I'd actually go further and say that the right course of action is point 1, users NOT developers (or DBAs) are responsible for data entry so they should be made to get it right. of course this leads to user hostile apps if validation conditions are complex, but then I'm a DBA so user hostile apps are just fine <VBG>.
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ****************************************** "Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message news:3CB303D7.E10FB3F_at_exesolutions.com...Received on Tue Apr 09 2002 - 10:23:47 CDT
> 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
>
 |
 |