Re: an ON-COMMIT trigger desperately needed

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.