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: Implementing complicated constraints

Re: Implementing complicated constraints

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: 29 Sep 2004 18:28:16 -0700
Message-ID: <73e20c6c.0409291728.502b773@posting.google.com>


Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0409280623.44b7bcf5_at_posting.google.com>...
>
> The fact that the ATXT column of A needs to be unique in relation to
> the CID value which does not appear in A indicates a relational design
> flaw in your tables.

Bingo. In one.

> Without changing the table design there are several options:
> 1- Perform the inserts/update via stored code that performs the
> validation up front avoiding the mutating table error

This might have problems with apps that enforce "no changes anywhere or else...".

>
> 2- substitute a view for the table and use instead of triggers to
> perform the validation and redirect the DML

I've used this technique to implement complex relationships - to do with subtyping and complex M-M - and even complete new schemas, without having to change any original app code. It works like a charm, is fast and is probably the most transparent way of achieving this. Perfect solution IMHO.

> 3- Use a combination of before and after, statement and row level
> triggers to work around the mutating table error. There are notes on
> this technique on metalink, but I have never managed to use the
> technique successfully to allow us to do what we needed in cases
> similar to what you want to do.

Too much trouble. The instead of trigger with a view is perfect for this.

I like Tom Kite's idea of a FBI, as proposed by HB. But I'm not sure it won't introduce other problems. IIRC, Tom introduced that technique at a time when instead of triggers were not yet available. He probably would have used the triggers nowadays. Received on Wed Sep 29 2004 - 20:28:16 CDT

Original text of this message

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