Re: an ON-COMMIT trigger desperately needed

Your example I believe was a deferred RI constraint. An example of a real world example would be: I have a table called CAR and a table called PARTS a car must have 4 wheels and a car must have a steering wheel. I want to make sure that no user other than the user actually doing the inserts can see a partial car in the database. (i.e. defered validation) At commit time if car does not have 4 wheels and a steering wheel transaction must error out and rollback. (similar to what happens below however the validation here is to  select count(*) from PARTS where part type = wheel  and if count != 4 then rollback
 and similar for sterring wheel)

-----Original Message-----

From:	Thomas Kyte [SMTP:tkyte_at_us.oracle.com]
Sent:	Thursday, July 06, 2000 12:38 PM
To:	Boyce, Keith G.
Subject:	RE: an ON-COMMIT trigger desperately needed

> -----Original Message-----
> From: Boyce, Keith G. [mailto:kgboyce_at_wellington.com]
> Sent: Thursday, July 06, 2000 10:32 AM
> To: 'Thomas Kyte '
> Subject: RE: an ON-COMMIT trigger desperately needed
>
>
> This is very interesting except that it does not address to issue
> of more complex validations that need to happen at commit time.
> For instance lets say that depending on the value of x in table p
> then in certain instances there must be a corresponding value in
> table c? It seems in this case I need to run some sort of procedure
> at commit time to validate the data. Is this functionality somehow
> supported?
>

but that functionality (depending on the value of X in table P then in certain instances there must be a corresponding value in table c) would be a
deferred RI constraint wouldn't it?

do you have a real world example of something that would need be done at commit time? There is usually a way to do it, maybe not in the forground
but in the background perhaps.

Thanks
In article <8jktvn$3f9$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> In article <8jipth$lqu$1_at_nnrp1.deja.com>,
> kgboyce_at_wellmanage.com wrote:
> > How do we get oracle to implement the following or does anyone
> > know an alternative solution???? Seems like a no brainer to me and
> > I am wondering why they wouldn't have provided this functionality
> > before.
> >
> > In order to enforce one to one relationships an ON-COMMIT trigger
> > in the Oracle Server which fires every time a commit call is
 executed
> > is needed. I would like to be able to do as follows.
> >
> > 1) for each row on my master table and my child table I will store
 the
> > primary key into a table of records in a package. There are
 existing
> > triggers to support this functionality in oracle.
> > 2) when the on-commit fires I will eliminate duplicate key values
> > from table of records and call a procedure
> > validate_transaction (primary key)
> > which will check the master table and it's child table to ensure
> > that rules (for instance 1-to-1) are adhered to. If not I
> > will report the relavent errors and not proceed with commit.
> > 3) If commit is successful I will clear table of records
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> Oracle8.0 and up you just do it declaritively:
>
> ops$tkyte_at_8i> drop table p cascade constraints;
> Table dropped.
>
> ops$tkyte_at_8i> drop table c cascade constraints;
> Table dropped.
>
> ops$tkyte_at_8i> create table p( x int primary key );
> Table created.
>
> ops$tkyte_at_8i> create table c( x int primary key );
> Table created.
>
> ops$tkyte_at_8i> alter table p add constraint
> 2 p_to_c foreign key
> 3 (x) references c(x)
> 4 initially deferred
> 5 /
> Table altered.
>
> ops$tkyte_at_8i> alter table c add constraint
> 2 c_to_p foreign key
> 3 (x) references p(x)
> 4 initially deferred
> 5 /
> Table altered.
>
> ops$tkyte_at_8i> insert into p values ( 1 );
> 1 row created.
>
> ops$tkyte_at_8i> insert into c values ( 1 );
> 1 row created.
>
> ops$tkyte_at_8i> commit;
> Commit complete.
>
> ops$tkyte_at_8i> insert into p values ( 2 );
> 1 row created.
>
> ops$tkyte_at_8i> commit;
> commit
> *
> ERROR at line 1:
> ORA-02091: transaction rolled back
> ORA-02291: integrity constraint (OPS$TKYTE.P_TO_C) violated - parent
> key not found
>
> ops$tkyte_at_8i> insert into c values ( 2 );
> 1 row created.
>
> ops$tkyte_at_8i> commit;
> commit
> *
> ERROR at line 1:
> ORA-02091: transaction rolled back
> ORA-02291: integrity constraint (OPS$TKYTE.C_TO_P) violated - parent
> key not found
>
> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> Howtos and such: http://osi.oracle.com/~tkyte/index.html
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 06 2000 - 00:00:00 CDT