Trigger question... please HELP!!!

    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 [Quoted] the trigger code such that 'after fail' message was shown in second SQL statement?

Thanks,
Jimmy Received on Thu Jun 03 1999 - 10:50:31 CEST