Re: More info

From: Doug Cowles <dcowles_at_nospambigfoot.com>
Date: Fri, 17 Dec 1999 17:20:23 -0500
Message-ID: <385AB727.3803C526@nospambigfoot.com>

I've added some more info :

Create table A( a NUMBER);
create table B ( a NUMBER);
alter table B modify a not null;

create trigger garbage

       after insert on A
       for each row
      begin
       insert into B values (:new,a);
       end;

insert into A values (1);
commit;
selec * from A;

1
select * from B;
---
1
insert into a values (NULL);

ORA-1400 cannot insert NULL into (staging.b.a.) ...
ORA-04088: error during execution of trigger;

commit;
select * from A;

1
select * from B;

1
drop trigger garbage;
insert into A values (NULL);
1 row created..

Oracle 8.1.5
So it seems that the event causing the trigger as well as the execution of the trigger itself,
I guess at least in the case of before and after triggers is one atomic unit of work that is
either committed or rolled back...
True?
Any way around it?

Doug Cowles wrote:

> It would appear if I have either a before or after insert trigger, and
> the trigger fails,
> then the insert fails as well. It it fair to say that the insert and
> the trigger execution
> are therefore part of the same unit of work ? (transaction?) It it fair
> to say that trigger are
> not asynchronous?
> Also, is there any way around this behavior - a way of making sure the
> insert completes
> normally regardless of wether the trigger has problems or not.
>
> Thanks,
> Dc.
>
> ------------------------------------------------------------------------
>
> Doug Cowles <dcowles_at_nospambigfoot.com>
> DBA
> IBM
>
> Doug Cowles
> DBA <dcowles_at_nospambigfoot.com>
> IBM
> Netscape Conference Address
> Netscape Conference DLS Server
> Please remove nospam from e-mail to reply.
> Additional Information:
> Last Name Cowles
> First Name Doug
> Version 2.1

Received on Fri Dec 17 1999 - 16:20:23 CST