Oracle 仿 MySQL MGR_MyISAM 功能

新增三個 Schema 相同的資料表，分別取為 MEMBERS0, MEMBERS1, MEMBERS_MERGE，欄位 ID 為 PRIMARY KEY w/ AUTO INCREMENT ， 分別為 MEMBERS0, 1 建立 after insert, after update 及 after delete 的 trigger

AD:

create or replace TRIGGER MEMBERS0_AD
AFTER DELETE ON MEMBERS0
FOR EACH ROW

DECLARE
    OLD_ID NUMBER;

BEGIN
    OLD_ID := :OLD.ID;

    DELETE FROM MEMBERS_MERGE WHERE ID = OLD_ID;

END;

AI:

create or replace TRIGGER MEMBERS0_AI
AFTER INSERT ON MEMBERS0
FOR EACH ROW

DECLARE
    NEW_ID NUMBER;
    NEW_FULL_NAME VARCHAR2(20);
    NEW_GENDER VARCHAR2(1);
    NEW_DEPARTMENT NUMBER;
    NEW_CREATE_DT DATE;

BEGIN
    NEW_ID := :NEW.ID;
    NEW_FULL_NAME := :NEW.FULL_NAME;
    NEW_GENDER := :NEW.GENDER;
    NEW_DEPARTMENT := :NEW.DEPARTMENT;
    NEW_CREATE_DT := :NEW.CREATE_DT;

    INSERT INTO MEMBERS_MERGE(ID, FULL_NAME, GENDER, DEPARTMENT, CREATE_DT) VALUES(NEW_ID, NEW_FULL_NAME, NEW_GENDER, NEW_DEPARTMENT, NEW_CREATE_DT); END;

AU:

create or replace TRIGGER MEMBERS0_AU
AFTER UPDATE ON MEMBERS0
FOR EACH ROW

DECLARE
    NEW_ID NUMBER;
    NEW_FULL_NAME VARCHAR2(20);
    NEW_GENDER VARCHAR2(1);
    NEW_DEPARTMENT NUMBER;
    NEW_CREATE_DT DATE;

BEGIN
    NEW_ID := :NEW.ID;
    NEW_FULL_NAME := :NEW.FULL_NAME;
    NEW_GENDER := :NEW.GENDER;
    NEW_DEPARTMENT := :NEW.DEPARTMENT;
    NEW_CREATE_DT := :NEW.CREATE_DT;

    UPDATE MEMBERS_MERGE SET ID = NEW_ID, FULL_NAME = NEW_FULL_NAME, GENDER = NEW_GENDER, DEPARTMENT = NEW_DEPARTMENT, CREATE_DT = NEW_CREATE_DT WHERE id = NEW_ID;

END;

考慮到若在 MERGE 資料表也建立 AU, AD triggers，會造成 trigger 的迴圈，故無法完全模擬 MySQL 的 MGR_MyISAM，替代方案就是讀寫分離，insert, update, delete 時使用各 MEMBERS，select 時使用 MERGE。