Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Trigger question... please HELP!!!

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

From: <chitnis_at_pspl.co.in>
Date: Thu, 03 Jun 1999 05:35:21 GMT
Message-ID: <7j546p$6vn$1@nnrp1.deja.com>


The trigger code is executed in the transaction scope of the sql statement. If constraints are violated either in the source sql or in the trigger code, the entire transaction is rollbacked.

This is the reason why you are not seeing any output.

Had you perform DML in the before trigger, the results of those DML would have been rollbacked. Since you are just piping output in the before trigger and since there are no rollback semantics for output streams, you may have thought that the before trigger was executed to completion by the database engine.

As soon as the constraint is violated, the transaction is rollbacked as a result of which the after trigger is not being fired.

In article <375641D7.E04D7E0B_at_comp.polyu.edu.hk>,   Jimmy <c6635500_at_comp.polyu.edu.hk> 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
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 03 1999 - 00:35:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US