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

 AFTER INSERT trigger never fires because AFTER INSERT event never happens. The order of events is:

1. BEFORE INSERT event
2. Record insertion  <-  unique constraint violated; record is not inserted
3. AFTER INSERT event <- so the event never happens

Jimmy 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?
>
> Thanks,
> Jimmy
Received on Tue Jun 08 1999 - 07:12:13 CDT