Oracle
可以通過條件數據庫約束來強制執行此業務邏輯嗎？
我試圖在數據庫中複製包含 Intranet C# Web 應用程序的業務邏輯，以便其他數據庫可以訪問它並在相同的規則下工作。如果不使用黑客，這個“規則”似乎很難實施。
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 );和
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));業務邏輯可以概括為
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這種邏輯可以體現在 Check 約束中還是醜陋的觸發器是唯一的方法？
編輯：
- 對於 CASE_STAGE.STAGE_ID 的所有值，必須在 CASE_STAGE.ID 中找到 PREVIOUS_STAGE_ID 的值
- 一旦不再是 CURRENT，應用程序就不允許從 CASE_RECOMMENDATION 中刪除（即當 CASE_STAGE.CURRENT 的值為 0 時，此階段已關閉且無法再更改，當 = 1 時，這是活動的階段或行並且現在可以更改。）
編輯：在這裡使用所有優秀的想法和評論是解決這個問題的有效方法
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) );在沒有建議的情況下使用現有軟體包插入 1646 階段時，錯誤是
ORA-12008: error in materialized view refresh path ORA-02290: check constraint (APPBASE.CASE_RECOMMENDATION_MV_C01) violated ORA-06512: at line 49任務完成！不是物化視圖的目的，而是比觸發器更好。
如果您有復雜的約束要在數據庫中“不可見”地應用，您可以通過創建一個物化視圖然後對其應用約束來實現。
在這種情況下，您可以使用 MV 外連接
CASE_RECOMMENDATION.CASE_STAGE_ID到CASE_STAGE.PREVIOUS_CASE_STAGE_ID. 然後應該檢查當 時這些都不為空CASE_STAGE.STAGE_ID = 1646，如下所示：--necessary for fast refresh create materialized view log on case_stage with rowid; create materialized view log on case_recommendation with rowid; create materialized view 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, cr.recommendation_id, case when cs.stage_id = 1646 then 'Y' else 'N' end do_chk, 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 (+); alter table mv add constraint mv_ck check ( (do_chk = 'Y' and previous_case_stage_id is not null and case_stage_id is not null ) or (do_chk = 'N') ); insert into CASE_STAGE values (1, 1, 1, sysdate, null, null, 1, null); insert into CASE_RECOMMENDATION values (1, 1, 1, sysdate, 1); commit; insert into CASE_STAGE values (2, 1646, 1, sysdate, null, null, 1, null); pro fails because previous_case_stage_id is null commit; SQL Error: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (CHRIS.MV_CK) violated 12008. 00000 - "error in materialized view refresh path" insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 2, 1, null); pro fails because previous_case_stage_id doesn't exist in CASE_RECOMMENDATION' commit; SQL Error: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (CHRIS.MV_CK) violated 12008. 00000 - "error in materialized view refresh path" pro succeeds ! insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 1, 1, null); commit; pro we can't delete stuff from case recommendation now delete CASE_RECOMMENDATION; commit; SQL Error: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (CHRIS.MV_CK) violated 12008. 00000 - "error in materialized view refresh path"MV 上的檢查約束只會在刷新時被呼叫，因此要使其成功執行，您需要確保在 COMMIT 上完成此操作。這將增加您的送出時間處理，因此您需要牢記以下幾點：
- 除非您的數據集非常小，否則 MV 應該是 REFRESH FAST。您如何建構 MV 以允許這樣做有一些限制
- 如果您在事務中有多個插入，則只會在您送出時拋出錯誤，這可能會使辨識有問題的語句變得更加困難
- 如果您有高水平的並發插入，這可能會導致一些並發問題
由於此解決方案在 SQL 層中實現了約束，因此它克服了過程解決方案中討論的一些並發問題。
更新
正如文森特所指出的，可以通過僅包含 stage_id = 1646 的行來減小 MV 的大小。可以重新編寫查詢以不消耗任何行，但我想不出該怎麼做現在：
create materialized view 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, 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.stage_id = 1646; alter table mv add constraint mv_ck check ( (previous_case_stage_id is not null and case_stage_id is not null) );