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?

Thanks for the info
Garry

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