Re: Trigger question... please HELP!!!

Hi,
  It raises an error while trying to insert and thus does not get to the after trigger. I am not sure there is any way out of this.

Regds,
Raghu Rajagopalan.

Thomas Kyte wrote:

> A copy of this was sent to Jimmy <c6635500_at_comp.polyu.edu.hk>
> (if that email address didn't require changing)
> On Thu, 03 Jun 1999 01:50:31 -0700, you wrote:
>
> >Hello all,
> >
> > I have a problem about trigger. Here is the following SQL code:
> >
> >create table aaa
> >(a varchar2(10) primary key,
> >b number);
> >
> >create or replace trigger before_aaa
> >before insert or update on aaa
> >begin
> > dbms_output.put_line('before success');
> >exception
> > when others then
> > dbms_output.put_line('before fail');
> >end;
> >/
> >
> >create or replace trigger after_aaa
> >after insert or update on aaa
> >begin
> > dbms_output.put_line('after success');
> >exception
> > when others then
> > dbms_output.put_line('after fail');
> >end;
> >/
> >
> > THen do the followings in SQL*PLUS:
> >
> >SQL> select * from aaa;
> >
> >no rows selected
> >
> >SQL> insert into aaa (a,b) values ('1',1);
> >before success
> >after success
> >
> >1 row created.
> >
> >SQL> insert into aaa (a,b) values ('1',1); -- second SQL statement
> >before success
> >insert into aaa (a,b) values ('1',1)
> >*
> >ERROR at line 1:
> >ORA-00001: unique constraint (AP.SYS_C00387) violated
> >
> > My question is, why second SQL statement, no raise exception in
> >after_aaa trigger? (i.e. no print 'after fail' message) How can I modify
> >the trigger code such that 'after fail' message was shown in second SQL
> >statement?
> >
>
> Thats not the way triggers work. The events when you insert a row are:
>
> - the BEFORE triggers are fired in *SOME ORDER*. It works from start to finish.
> - the BEFORE for each row triggers are fired in *SOME ORDER*
> - constraints and such are validated
> - the AFTER for each row triggers are fired in *SOME ORDER*
> - the AFTER triggers fire in *SOME ORDER*
>
> If any of the above steps fail -- the remaining steps do not proceed.
>
> >Thanks,
> >Jimmy
>
> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Thu Jun 03 1999 - 10:29:02 CDT