> > A developer emailed me indicating he was able to violate this constraint > without error. > Did the developer say how that was done? Does anyone see a problem with relying on this method to enforce the rule? > Theoretically at least. > Not in theory, no. You do need to be aware that the Mview efficiently enforces the rule. This has two dimensions: 1) you would want the refresh to be fast. In this case only refresh the row of the project that got changed in the transaction, and not all other projects. When business rules become complex, Mviews might not be efficient enough, due to the fact that they're not 'fast refreshable' anymore. 2) there is always serialization involved when (multi-row) business rules require validation. Rob van Wijk wrote a blogpost on this, when you use Mviews to do so: http://rwijk.blogspot.com/2010/01/enq-ji-contention.html On Wed, Jul 21, 2010 at 8:28 PM, Stephens, Chris <Chris.Stephens@xxxxxxx>wrote: > 10.2.0.4 on RHEL 5 > > > > Unfortunately I didn’t think to look at *_mviews prior to fixing the > issue. We are using a materialized view to enforce a business rule of no > current project can have a sum(fraction) != 100. > > > > Base table: > > > > SQL> desc allocation > > Name Null? Type > > ----------------------------------------- -------- > ---------------------------- > > FRACTION NOT NULL NUMBER(5) > > PROJECTNUMBER NOT NULL VARCHAR2(127) > > CODE NOT NULL CHAR(3) > > ISCURRENT NOT NULL NUMBER(1) > > CHANGEDATE NOT NULL DATE > > PERIODID NOT NULL NUMBER(10) > > > > Materialized view: > > > > CREATE MATERIALIZED VIEW “ALLOCATIONTOTALS" REFRESH FORCE ON COMMIT USING > DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE > > AS > > (SELECT projectnumber, > > periodid, > > SUM(fraction) AS total > > FROM allocation > > WHERE iscurrent = 1 > > GROUP BY projectnumber, > > periodid > > ); > > > > Check constraint on the mview: > > > > total = 100 > > > > enabled, not deferrable, validated. > > > > A developer emailed me indicating he was able to violate this constraint > without error. I confirmed. As soon as I dropped and recreated the mview > (after cleaning up the data), I was unable to duplication the issue. > > > > Does anyone see a problem with relying on this method to enforce the rule? > Theoretically at least. > > > > Chris > > CONFIDENTIALITY NOTICE: > This message is intended for the use of the individual or entity to which > it is addressed and may contain information that is privileged, confidential > and exempt from disclosure under applicable law. If the reader of this > message is not the intended recipient or the employee or agent responsible > for delivering this message to the intended recipient, you are hereby > notified that any dissemination, distribution or copying of this > communication is strictly prohibited. If you have received this > communication in error, please notify us immediately by email reply. > > > -- Toon Koppelaars RuleGen BV Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13