materialized view to enforce business rule
- From: "Stephens, Chris" <Chris.Stephens@xxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jul 2010 13:28:47 -0500
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.
Other related posts:
