最も単純な例は、値、値の開始日、値の終了日の3つのフィールドからの値の履歴テーブルです。 論理的に正しい履歴は、値の有効期間が重複するレコードを持つことはできません。 隣接する2つの値のアクション境界を変更するには、2つの日付を変更する必要があります。前のレコードの前の値の終了日と次のレコードの次の値の開始日です。 時間の経過とともに値の変化のフロンティアを進め、最初のステップで最初のレコードの値の有効期限を再配置すると、論理的に誤ったデータセットが得られます。 そのため、テーブルトリガーの問題を解決することは不可能です。これらは、各データ変更演算子に対して機能します。
実際のタスクは、最も単純な例とは少し異なります。 データセットは1ダースのテーブルに配置され、ビジネス制御ルールアルゴリズムにより、リンクを介して他のサーバー上のAPIにアクセスする400行の手順が作成されました。
このような制御を実装するには、COMMITイベントのトランザクションで1回だけ起動するトリガーが必要でした。また、ビジネスロジック制御手順を実行した結果としてトランザクションをロールバックする機能が必要でした。 そのようなトリガーが見つかりました。
CREATE OR REPLACE TRIGGER <mv_as> AFTER DELETE OR INSERT OR UPDATE ON MV
MVは次のとおりです。
CREATE MATERIALIZED VIEW MV REFRESH COMPLETE ON COMMIT AS SELECT <,,,,> FROM <tab>;
例として実装の詳細を検討してください。 データセット。
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(17,2), DEPTNO NUMBER(2)); CREATE TABLE DEPT (DEPTNO NUMBER(2) NOT NULL, DNAME VARCHAR2(14), LOC VARCHAR2(13)); insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK'); insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS'); insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO'); insert into DEPT (DEPTNO, DNAME, LOC) values (31, 'OPERATIONS', 'CHICAGO'); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7369, 'SMITH', 'CLERK', 7902, TIMESTAMP '1980-12-17 00:00:00', 2800, null, 20); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7499, 'ALLEN', 'SALESMAN', 7698, TIMESTAMP '1981-02-20 00:00:00', 1600, 300, 31); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7521, 'WARD', 'SALESMAN', 7698, TIMESTAMP '1981-02-22 00:00:00', 1250, 500, 30); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7566, 'JONES', 'MANAGER', 7839, TIMESTAMP '1981-04-02 00:00:00', 2975, null, 20); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7650, 'MARTIN', 'SALESMAN', 7698, TIMESTAMP '1981-09-28 00:00:00', 1251, 1400, 30); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7698, 'BLAKE', 'MANAGER', 7839, TIMESTAMP '1981-05-01 00:00:00', 2850, null, 30); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7782, 'CLARK', 'MANAGER', 7839, TIMESTAMP '1981-06-09 00:00:00', 2450, null, 10); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7708, 'SCOTT', 'ANALYST', 7566, TIMESTAMP '1982-12-09 00:00:00', 3000, null, 20); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7639, 'KING', 'PRESIDENT', null, TIMESTAMP '1981-11-17 00:00:00', 5000, null, 10); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7844, 'TURNER', 'SALESMAN', 7698, TIMESTAMP '1981-09-10 00:00:00', 1500, 0, 30); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7876, 'ADAMS', 'CLERK', 7788, TIMESTAMP '1982-01-12 00:00:00', 1100, null, 20); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7900, 'JAMES', 'CLERK', 7698, TIMESTAMP '1981-12-03 00:00:00', 950, null, 31); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7902, 'FORD', 'ANALYST', 7566, TIMESTAMP '1981-12-03 00:00:00', 3000, null, 20); insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (7934, 'MILLER', 'CLERK', 7782, TIMESTAMP '1982-01-23 00:00:00', 1300, null, 10); alter table emp add constraint m_k primary key(empno); alter table dept add constraint dept_pk primary key(deptno); alter table emp add constraint emp_fk_dept foreign key (deptno) references dept;
サンプルデータは、ユニットとユニットの場所に関する情報を持つ「従業員」タイプのエンティティのセットです。 このデータのビジネスルールを実装して、1つの都市で「CLERK」という職位を持つ従業員の数を2以下に制限してみましょう。
一般的なケースでは、いくつかのビジネス制御ルールがあり、1つのトランザクションで、複数の従業員の情報を変更できます。 したがって、コミット時には、2組の情報が必要です。
-変更された一連のフィールドは、監視する必要があるビジネスルールのリストを決定します。
-管理対象の従業員の識別子のセット。
ビジネス制御ルールとその複雑さの実用的なリストにより、サーバーに重大な負荷をかけることなく、実装されたすべてのルールに従って変更された各従業員をチェックできます。 この仮定により、この場合、整合性制約の実装を簡素化できます。
現在のトランザクションによって変更された従業員の識別子のセットを含むテーブルを作成します。
create table emp_chk ( emp_no NUMBER, i NUMBER); alter table emp_chk add constraint PK_emp_no primary key (emp_no);
コントロールルールの情報を含むすべてのテーブルで、emp_chkに変更された従業員の識別子を挿入するトリガーを設定します。 トリガーに関するコメント。 制御機能の戦闘使用の顧客は、ORACLE-9との互換性を必要としたため、トリガーは複合的ではありません。
コンストラクトを無効にする機能は、バッチ関数var_chk.chk_onによって実装されます。 この目的で関数を使用すると、静的に（構成テーブルを介して）コントロールを制御できるだけでなく、動的に（たとえば、異なるデータベースセッションに対して）コントロールを制御できます。 パッケージの全文は後で提供されます。
MERGEの使用は、1人のオペレーターによる変更を実行したいという要望が原因です。 emp_chk.iフィールドは、MERGEを使用するための料金です。 フレーズWHEN MATCHEDのないMERGEの書き込みに失敗しました。
CREATE OR REPLACE TRIGGER emp_chk_ar AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; MERGE INTO emp_chk a USING (SELECT nvl(:new.empno, :old.empno) AS emp_no , 1 AS i FROM dual ) b ON (a.emp_no = b.emp_no) WHEN NOT MATCHED THEN INSERT (a.emp_no, ai) VALUES (b.emp_no, bi) WHEN MATCHED THEN UPDATE SET ai = bi; END emp_chk_ar; CREATE OR REPLACE TRIGGER dept_chk_ar AFTER DELETE OR INSERT OR UPDATE ON dept FOR EACH ROW BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; MERGE INTO emp_chk a USING ( SELECT emp.empno AS emp_no , 1 AS i FROM emp WHERE emp.deptno = NVL(:new.deptno, :old.deptno) ) b ON (a.emp_no = b.emp_no) WHEN NOT MATCHED THEN INSERT (a.emp_no, ai) VALUES (b.emp_no, bi) WHEN MATCHED THEN UPDATE SET ai = bi; END dept_chk_ar;
emp_chkテーブルは、次のトリガーによって新しいトランザクションの開始時にクリアされます。 バッチ変数var_chk.first_dml_in_commitがクリーンアップを制御します。
CREATE OR REPLACE TRIGGER emp_chk_bs BEFORE DELETE OR INSERT OR UPDATE ON emp BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; IF var_chk.first_dml_in_commit = 1 THEN DELETE FROM emp_chk; END IF; var_chk.first_dml_in_commit := 0 ; END emp_chk_bs; CREATE OR REPLACE TRIGGER dept_chk_bs BEFORE DELETE OR INSERT OR UPDATE ON dept BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; IF var_chk.first_dml_in_commit = 1 THEN DELETE FROM emp_chk; END IF; var_chk.first_dml_in_commit := 0; END dept_chk_bs;
マテリアライズドビューを作成します。
CREATE MATERIALIZED VIEW chk_emp_mv REFRESH COMPLETE ON COMMIT AS SELECT emp_no,i FROM emp_chk;
var_chk.first_dml_in_commitを初期化するトリガーにより、トランザクションの開始時にEMP_CHKが確実にクリアされます。
CREATE OR REPLACE TRIGGER chk_emp_mv_bs BEFORE DELETE OR INSERT OR UPDATE ON chk_emp_mv BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; var_chk.first_dml_in_commit := 1 ; END chk_emp_mv_bs;
実際にビジネス制御を開始するトリガー。
CREATE OR REPLACE TRIGGER chk_emp_mv_as AFTER DELETE OR INSERT OR UPDATE ON chk_emp_mv DECLARE v_result NUMBER; v_errtxt VARCHAR2(512); BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; FOR cur IN (SELECT t.emp_no FROM CHK_EMP_MV t) LOOP SELECT XMLELEMENT("EMP", XMLAGG(XMLELEMENT("EMPNO",tb.empno, XMLELEMENT("ENAME", tb.ename), XMLELEMENT("JOB", tb.job), XMLELEMENT("MGR", tb.mgr), XMLELEMENT("SAL", tb.sal), XMLELEMENT("DEPTNO", tb.DEPTNO), XMLELEMENT("COMM", tb.comm), XMLELEMENT("HIREDATE", TO_CHAR(tb.hiredate,'dd.mm.yyyy')) ,(SELECT XMLELEMENT("DEPT", XMLAGG(XMLELEMENT("DEPTNO",d.deptno, XMLELEMENT("DNAME",d.dname), XMLELEMENT("LOC",d.loc) ))) FROM dept d WHERE d.deptno = tb.deptno ) )) ).GETCLOBVAL() INTO var_chk.var_emp_val FROM ( SELECT * FROM EMP WHERE emp.empno = cur.emp_no ) tb GROUP BY empno,sal,mgr,job,hiredate,ename,deptno,comm ; -- v_result := emp_logic(cur.emp_no,v_errtxt); var_chk.write_log(v_result,v_errtxt); IF v_result = 1 THEN RAISE_APPLICATION_ERROR (-20555,v_errtxt); END IF; END LOOP; END chk_emp_mv_as;
テキストCHK_EMP_MV_ASに関するいくつかのコメント。 構成要素の機能のデバッグと監視は、ログを記録することで促進できます。 エラーが発生した場合、通常、コミット用に提示されたデータセットはロールバックされて失われることを考慮してください。 この実装では、最終的な処理ステータスだけでなく、処理結果に関係なくcommit-aに提示された変更バージョンの従業員のデータセット全体がログに書き込まれます。 データセットのスナップショットは、emp_chk_log.XMLフィールドに配置されます。 ログはバッチ関数var_chk.write_logによってテーブルに書き込まれます。
create table emp_chk_log ( ts DATE, status NUMBER, XML CLOB );
すべてのビジネスルールは、個別のemp_logic関数で実装されます。 この関数はパッケージのメンバーではありません。 これにより、ビジネスの開発と保守における設計ルールとその機能のシステムメカニズムのレイヤーを分離できます。 以下は、var_chkパッケージのテキストです。
CREATE OR REPLACE PACKAGE var_chk AS first_dml_in_commit NUMBER; var_emp_val CLOB; FUNCTION chk_on return NUMBER; PROCEDURE write_log (p_status NUMBER ,p_err_txt VARCHAR2); END;
CREATE OR REPLACE PACKAGE BODY var_chk AS -------------------- FUNCTION chk_on RETURN NUMBER IS -- 1 - -- 0 - BEGIN RETURN 1; END chk_on; --------------------- PROCEDURE write_log (p_status NUMBER ,p_err_txt VARCHAR2) is PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO emp_chk_log (ts,status,xml,err_txt) VALUES (sysdate,p_status,var_emp_val,SUBSTR(p_err_txt,1,512)); COMMIT; END write_log; --------------------- BEGIN first_dml_in_commit :=1; dbms_lob.createtemporary(var_emp_val,true); END;
ビジネスルールを制御する機能。
CREATE OR REPLACE FUNCTION emp_logic (p_emp_no NUMBER ,p_errtxt OUT VARCHAR2 ) RETURN NUMBER IS v_emp_count NUMBER; v_emp_loc dept.loc%TYPE; BEGIN SELECT dept.loc,COUNT(*) INTO v_emp_loc,v_emp_count FROM emp, dept, ( SELECT emp.job, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.empno = p_emp_no AND emp.job = 'CLERK' ) p WHERE emp.deptno = dept.deptno AND p.loc=dept.loc AND p.job=emp.job GROUP BY dept.loc ; IF v_emp_count > 2 THEN p_errtxt:=': '||v_emp_loc||' 2 '; RETURN 1; END IF; RETURN 0; END emp_logic;
外出先で自転車を確認してください。
SQL> UPDATE EMP SET JOB='CLERK' WHERE EMPNO=7708; 1 row updated. SQL> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: : DALLAS 2 ORA-06512: at "ZH.CHK_EMP_MV_AS", line 43 ORA-04088: error during execution of trigger 'ZH.CHK_EMP_MV_AS' SQL> UPDATE EMP SET JOB='CLERK' WHERE EMPNO=7369; 1 row updated. SQL> commit; Commit complete. SQL> select ts,status,to_char(xml) from emp_chk_log; TS STATUS --------------- ---------- TO_CHAR(XML) -------------------------------------------------------------------------------- 30-MAR-16 1 <EMP><EMPNO>7708<ENAME>SCOTT</ENAME><JOB>CLERK</JOB><MGR>7566</MGR><SAL>3000</SA L><DEPTNO>20</DEPTNO><COMM></COMM><HIREDATE>09.12.1982</HIREDATE><DEPT><DEPTNO>2 0<DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></DEPTNO></DEPT></EMPNO></EMP> TS STATUS --------------- ---------- TO_CHAR(XML) -------------------------------------------------------------------------------- 30-MAR-16 0 <EMP><EMPNO>7369<ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><SAL>2800</SA L><DEPTNO>20</DEPTNO><COMM></COMM><HIREDATE>17.12.1980</HIREDATE><DEPT><DEPTNO>2 0<DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></DEPTNO></DEPT></EMPNO></EMP> SQL>
このソリューションの実際の実装は、2015年の春から3ダースのセントラルオフィスサーバーとブランチで実行されています。