Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews1.google.com!not-for-mail From: wizofoz2k@yahoo.com.au (Noons) Newsgroups: comp.databases.oracle,comp.databases.oracle.server Subject: Re: Implementing complicated constraints Date: 29 Sep 2004 18:28:16 -0700 Organization: http://groups.google.com Lines: 39 Message-ID: <73e20c6c.0409291728.502b773@posting.google.com> References: <2687bb95.0409280623.44b7bcf5@posting.google.com> NNTP-Posting-Host: 203.28.13.57 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1096507697 4923 127.0.0.1 (30 Sep 2004 01:28:17 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 30 Sep 2004 01:28:17 +0000 (UTC) Xref: dp-news.maxwell.syr.edu comp.databases.oracle:28811 comp.databases.oracle.server:225922 Mark.Powell@eds.com (Mark D Powell) wrote in message news:<2687bb95.0409280623.44b7bcf5@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.