Can this business logic be enforced by a conditional database constraint? 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) &gt; 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.
If CASE_RECOMMENDATION.CASE_STAGE_ID is unique, you can use referential integrity with a virtual column (11g+) to make it conditional:

alter table CASE_RECOMMENDATION ADD CONSTRAINT unique_case_stage unique (CASE_STAGE_ID);

-- virtual column only defined when stage_id=1646
alter table CASE_STAGE add 
   (case_1646 as (case when stage_id=1646 then previous_case_stage_id end));

-- check that the virtual column is defined when stage_id=1646
alter table case_stage add 
    constraint chk_1646 check ( stage_id!=1646 or previous_case_stage_id is not null);

-- referential integrity
alter table case_stage add 
     constraint fk_1646 foreign key (case_1646) 
     references case_recommendation (case_stage_id);


Let's check:

SQL&gt; insert into  CASE_STAGE values (1, 1, 1, sysdate, null, null, 1, null, default);

1 row(s) inserted.

SQL&gt; insert into CASE_RECOMMENDATION values (1, 1, 1, sysdate, 1);

1 row(s) inserted.

SQL&gt; -- fails because previous_case_stage_id is null
SQL&gt; insert into CASE_STAGE values (2, 1646, 1, sysdate, null, null, 1, null, default);

ORA-02290: check constraint (VNZ_TEST3.CHK_1646) violated

SQL&gt; -- fails because previous_case_stage_id doesn't exist in CASE_RECOMMENDATION
SQL&gt; insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 2, 1, null, default); 

ORA-02291: integrity constraint (VNZ_TEST3.FK_1646) violated - parent key not found

SQL&gt; -- succeeds !
SQL&gt; insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 1, 1, null, default); 

1 row(s) inserted.