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

[Quoted] 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