I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to implement without using hacks.
CREATE TABLE CASE_STAGE
(
ID NUMBER(9) PRIMARY KEY NOT NULL,
STAGE_ID NUMBER(9) NOT NULL,
CASE_PHASE_ID NUMBER(9) NOT NULL,
DATE_CREATED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
END_REASON_ID NUMBER(9),
PREVIOUS_CASE_STAGE_ID NUMBER(9),
"CURRENT" NUMBER(1) NOT NULL,
DATE_CLOSED TIMESTAMP(6) DEFAULT NULL
);
and
CREATE TABLE CASE_RECOMMENDATION
(
CASE_ID NUMBER(9) NOT NULL,
RECOMMENDATION_ID NUMBER(9) NOT NULL,
"ORDER" NUMBER(9) NOT NULL,
DATE_CREATED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
CASE_STAGE_ID NUMBER(9) NOT NULL
);
ALTER TABLE CASE_RECOMMENDATION ADD (
CONSTRAINT SYS_C00000
PRIMARY KEY
(CASE_ID, RECOMMENDATION_ID));
The business logic can be summed up as
When Inserting into CASE_STAGE
If CASE_STAGE.STAGE_ID = 1646
THEN
CASE_STAGE.PREVIOUS_STAGE_ID must be found in CASE_RECOMMENDATION.CASE_STAGE_ID
Can this logic be embodied in a Check constraint or is an ugly trigger the only way?
Edit:
- For all values of CASE_STAGE.STAGE_ID the value for PREVIOUS_STAGE_ID must be found in CASE_STAGE.ID
- The application does not allow deletions from CASE_RECOMMENDATION once it is no longer CURRENT ( ie when the value of CASE_STAGE.CURRENT is 0 this stage is closed and can no longer be changed, when = 1 this is the stage, or row, that is active and can be changed now.)
Edit: using all the excellent ideas and comments here is a working solution to this problem
CREATE MATERIALIZED VIEW LOG ON CASE_STAGE
TABLESPACE USERS
STORAGE (
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON CASE_RECOMMENDATION
TABLESPACE USERS
STORAGE (
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOPARALLEL
WITH ROWID;
CREATE MATERIALIZED VIEW CASE_RECOMMENDATION_MV REFRESH FAST ON COMMIT AS
SELECT
cr.ROWID cr_rowid, --necessary for fast refresh
cs.ROWID cs_rowid, --necessary for fast refresh
cr.case_id,
cs.stage_id,
cr.recommendation_id
cr.case_stage_id,
cs.previous_case_stage_id
FROM CASE_RECOMMENDATION cr,
case_stage cs
WHERE cs.previous_case_stage_id = cr.case_stage_id (+)
AND CS.PREVIOUS_CASE_STAGE_ID IS NOT NULL
AND EXTRACT (YEAR FROM CS.DATE_CREATED) > 2010 --covers non conforming legacy data
AND CR.RECOMMENDATION_ID IS NULL
AND cs.stage_id =1646;
--this last line excludes everything but problem cases due to the outer join
ALTER TABLE CASE_RECOMMENDATION_MV ADD CONSTRAINT CASE_RECOMMENDATION_ck CHECK (
(previous_case_stage_id IS NOT NULL AND case_stage_id IS NOT NULL)
);
When inserting a 1646 stage using existing packages without a recommendation the error was
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (APPBASE.CASE_RECOMMENDATION_MV_C01) violated
ORA-06512: at line 49
Job done! Not what a materialized view was intended for but better than a trigger.
CASE_RECOMMENDATION.CASE_STAGE_ID
unique? – Vincent Malgrat Sep 13 '12 at 15:571646
? Is changing the design (splitting table into two) an option? – ypercubeᵀᴹ Sep 13 '12 at 16:50