Конструктивные комментарии общества к посту Сложное ограничение целостности (constraint) в котором была представлена реализация сложного констрейнта БД ORACLE заставили глубже погрузиться в тему. Тем более что функционал стоит в боевой эксплуатации и оставлять в нем шероховатости было бы некрасиво.
Что нового.
Похоже удалось обеспечить многопользовательский режим. Уважаемый xtender совершенно справедливо отметил этот недостаток предыдущей реализации.
Бизнесправила контрольного примера ( сферический слон ) на тестовом наборе данных ( в вакууме ) усложнены. Более сложный пример нагляднее отражает не самую плохую масштабируемость решения — текст триггеров на разных таблицах практически идентичен. Добавление любого количества новых бизнесправил без изменения состава участвующих таблиц (emp, dept, emp_attr_vals) потребует модификации только функции emp_logic. Наверно гуру смогут реализовать этот вариант констрейнта по ТомуК (fast refresh mview) но всему есть предел. Сам Том считает что когда задачу не реализовать одним sql (mview) пора решать на plsql.
У предлагаемого решения есть недостаток — не обкатано на продакшн под нагрузкой.
Реализация.
Таблицы бизнесинформации.
CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) ); CREATE UNIQUE INDEX "DEPT_PK" ON "DEPT" ("DEPTNO"); ALTER TABLE "DEPT" ADD CONSTRAINT "DEPT_PK" PRIMARY KEY ("DEPTNO") ENABLE;
Данные
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'); CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "COMM" NUMBER(17,2), "DEPTNO" NUMBER(2,0) );
Данные
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7369','SMITH','CLERK','7902',to_date('17.12.1980','DD.MM.RRRR '),'100','20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7499','ALLEN','SALESMAN','7698',to_date('20.02.1981','DD.MM.RRRR '),'300','31'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7521','WARD','SALESMAN','7698',to_date('22.02.1981','DD.MM.RRRR '),'10','30'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7566','JONES','MANAGER','7777',to_date('02.04.1981','DD.MM.RRRR '),'4','20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7698','BLAKE','MANAGER','7777',to_date('01.05.1981','DD.MM.RRRR '),null,'30'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7782','CLARK','MANAGER','7777',to_date('09.06.1981','DD.MM.RRRR '),null,'10'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7708','SCOTT','ANALYST','7566',to_date('09.12.1982','DD.MM.RRRR '),'7708','20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7777','KING','PRESIDENT',null,to_date('17.11.1981','DD.MM.RRRR '),null,'10'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7844','TURNER','SALESMAN','7698',to_date('10.09.1981','DD.MM.RRRR '),'0','30'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7876','ADAMS','CLERK','7639',to_date('12.01.1982','DD.MM.RRRR '),null,'20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7900','JAMES','CLERK','7698',to_date('03.12.1981','DD.MM.RRRR '),null,'31'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7902','FORD','ANALYST','7566',to_date('03.12.1981','DD.MM.RRRR '),null,'20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7934','MILLER','CLERK','7782',to_date('23.01.1982','DD.MM.RRRR '),null,'10');
CREATE UNIQUE INDEX "ЕMР_РK" ON "EMP" ("EMPNO"); ALTER TABLE "EMP" MODIFY ("EMPNO" NOT NULL ENABLE); ALTER TABLE "EMP" ADD CONSTRAINT "ЕMР_РK" PRIMARY KEY ("EMPNO") ENABLE; ALTER TABLE "EMP" ADD CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE; CREATE TABLE "EMP_ATTR_VALS" ( "DATE_BEG" DATE, "DATE_END" DATE, "VAL" VARCHAR2(64), "EMP_ATTR" VARCHAR2(32), "EMPNO" NUMBER ); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("DATE_BEG" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("DATE_END" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("VAL" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("EMP_ATTR" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("EMPNO" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" ADD CONSTRAINT "EMP_AV_FK" FOREIGN KEY ("EMPNO") REFERENCES "EMP" ("EMPNO") ENABLE;
Данные
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'150','SALARY','7521'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'180','SALARY','7521'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'B','EDUCATION','7521'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'C','EDUCATION','7521'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2017','DD.MM.RRRR'),'150','SALARY','7844'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2017','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'200','SALARY','7844'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'B','EDUCATION','7844'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7844'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'200','SALARY','7708'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'300','SALARY','7708'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.07.2016','DD.MM.RRRR'),'B','EDUCATION','7708'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.07.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7708'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'200','SALARY','7902'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'300','SALARY','7902'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7902'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7566'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'400','SALARY','7566'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7566'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7698'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'400','SALARY','7698'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7698'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7782'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'450','SALARY','7782'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'F','EDUCATION','7782'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'10000','SALARY','7777'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'A','EDUCATION','7777');
Таблица лога.
CREATE TABLE "EMP_CHK_LOG" ( "TS" TIMESTAMP (6), "STATUS" NUMBER, "XML" CLOB, "ERR_TXT" VARCHAR2(512) );Для работы с транзакциями схеме c примером (ZX) потребуется некоторый доступ к схеме SYS.
CREATE OR REPLACE VIEW sys.v_transaction AS SELECT xidusn, xidslot, xidsqn,ses_addr, start_scn FROM v$transaction; GRANT SELECT ON sys.v_transaction TO zx; GRANT EXECUTE on sys. dbms_transaction TO zx; GRANT CREATE MATERIALIZED VIEW TO zx;Базовая таблица матвью.
CREATE TABLE "EMP_CHK" ( "EMP_NO" NUMBER, "LTRANS_ID" VARCHAR2(64)); CREATE UNIQUE INDEX "EMP_CHK_PK" ON "EMP_CHK" ("LTRANS_ID"); ALTER TABLE "EMP_CHK" ADD CONSTRAINT "EMP_CHK_PK" PRIMARY KEY ("LTRANS_ID") ENABLE; ALTER TABLE "EMP_CHK" MODIFY ("LTRANS_ID" NOT NULL ENABLE);Создаем materialized view.
CREATE MATERIALIZED VIEW chk_emp_mv REFRESH COMPLETE ON COMMIT AS SELECT emp_no, ltrans_id FROM emp_chk;Многопользовательский режим работы обеспечен следующим образом. В начале каждой транзакции из базовой таблицы матвью процедурой var_chk.cls_emp_chk информация завершенных транзакций удаляются построчно. Использование автономной транзакции обеспечивает очистку независимо от исхода (commit/rollback) текущей транзакции. В предыдущем варианте очищалась а следовательно и блокировалась вся таблица до окончания текущей транзакции.
create or replace PACKAGE var_chk AS TYPE tab_str IS TABLE OF VARCHAR2(64); first_dml_in_commit NUMBER; var_emp_val CLOB; FUNCTION chk_on RETURN NUMBER; PROCEDURE write_log (p_status NUMBER ,p_err_txt VARCHAR2); PROCEDURE cls_emp_chk; END var_chk; 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; --------------------- PROCEDURE cls_emp_chk IS v_trans_arr tab_str := tab_str(); v_i NUMBER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN FOR cur1 IN ( SELECT ltrans_id FROM emp_chk) loop v_trans_arr.extend; v_trans_arr(v_trans_arr.last) := cur1.ltrans_id; END LOOP; FOR i IN 1 .. v_trans_arr.count LOOP SELECT count(*) INTO v_i FROM sys.v_transaction t WHERE to_char(t.xidusn)||'.'||to_char(t.xidslot)||'.'||to_char(t.xidsqn) = v_trans_arr(i); IF v_i = 0 THEN DELETE FROM emp_chk WHERE ltrans_id = v_trans_arr(i); END IF; COMMIT; END LOOP; END; --------------------- BEGIN first_dml_in_commit :=1; dbms_lob.createtemporary(var_emp_val,true); END var_chk; create or replace TRIGGER dept_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 VAR_CHK.cls_EMP_CHK; END IF; var_chk.first_dml_in_commit := 0 ; END dept_bs; create or replace TRIGGER emp_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 var_chk.cls_emp_chk; END IF; var_chk.first_dml_in_commit := 0; END emp_bs; create or replace TRIGGER emp_attr_vals_bs BEFORE DELETE OR INSERT OR UPDATE ON emp_attr_vals BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; IF var_chk.first_dml_in_commit = 1 THEN var_chk.cls_emp_chk; END IF; var_chk.first_dml_in_commit := 0 ; END emp_attr_vals_bs;По каждому DMLю на любой из таблиц бизнесинформации в базовую таблицу матвью пишется идентификатор контролируемой сущности (в нашем примере сотрудник) и идентификатор текущей транзакции.
create or replace TRIGGER emp_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, sys.dbms_transaction.local_transaction_id AS ltrans_id FROM dual) b ON (a.emp_no = b.emp_no) WHEN NOT MATCHED THEN INSERT (a.emp_no, a.ltrans_id) VALUES (b.emp_no, b.ltrans_id) WHEN MATCHED THEN UPDATE SET a.ltrans_id = b.ltrans_id; END emp_ar; create or replace TRIGGER dept_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, dbms_transaction.local_transaction_id AS ltrans_id 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, a.ltrans_id) VALUES (b.emp_no, b.ltrans_id) WHEN MATCHED THEN UPDATE SET a.ltrans_id = b.ltrans_id; END dept_ar; create or replace TRIGGER emp_attr_vals_ar AFTER DELETE OR INSERT OR UPDATE ON emp_attr_vals 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, dbms_transaction.local_transaction_id AS ltrans_id FROM emp WHERE emp.empno = NVL(:new.empno, :old.empno) ) b ON (a.emp_no = b.emp_no) WHEN NOT MATCHED THEN INSERT (a.emp_no, a.ltrans_id) VALUES (b.emp_no, b.ltrans_id) WHEN MATCHED THEN UPDATE SET a.ltrans_id = b.ltrans_id; END emp_attr_vals_ar;Функция бизнесконтроля.
create or replace FUNCTION emp_logic (p_emp_no NUMBER ,p_errtxt OUT VARCHAR2 ) RETURN NUMBER IS v_emp_count NUMBER; v_emp_loc VARCHAR2(16); v_job VARCHAR2(32); v_sal NUMBER; BEGIN ----------------------------------- SELECT dept.loc,COUNT(*) INTO v_emp_loc,v_emp_count FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.LOC in ( SELECT dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.empno = p_emp_no ) AND emp.job = 'CLERK' GROUP BY dept.loc; IF v_emp_count > 2 THEN p_errtxt:='Ошибка. В филиале '||v_emp_loc||' больше 2х клерков'; RETURN 1; END IF; ------------------------------------- FOR cur_d IN ( SELECT SUM(delt) sdelt, emp_attr FROM( SELECT t.date_beg, t.date_end,t.emp_attr, NVL(lead(t.date_beg) over(partition by t.empno, t.emp_attr order by t.date_beg, t.date_end)-1, t.date_end) - t.date_end delt FROM emp_attr_vals t WHERE t.empno = p_emp_no ) GROUP BY emp_attr ) LOOP IF cur_d.sdelt != 0 THEN p_errtxt:='Ошибка. Пересечение дат в истории параметра '||cur_d.emp_attr; RETURN 1; END IF; END LOOP; --------------------------------- SELECT emp.job, nvl((SELECT to_number(val) FROM emp_attr_vals eav WHERE eav.emp_attr = 'SALARY' AND SYSDATE BETWEEN eav.date_beg AND eav.date_end AND emp.empno = eav.empno ),0) sal INTO v_job ,v_sal FROM emp WHERE emp.empno = p_emp_no; IF v_job != 'PRESIDENT' AND v_sal > 999 THEN p_errtxt:='Ошибка. Только президент может иметь зарплату больше 999'; RETURN 1; END IF; RETURN 0; END emp_logic;Триггера на матвью.
Запускающий очистку данных предыдущей транзакции из базовой таблицы матвью.
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); v_ltrans_id VARCHAR2(64); BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; v_ltrans_id:=dbms_transaction.local_transaction_id; FOR cur IN (SELECT t.emp_no FROM chk_emp_mv t where t.ltrans_id=v_ltrans_id) LOOP SELECT XMLELEMENT("EMP", XMLAGG(XMLELEMENT("EMPNO",tb.empno, XMLELEMENT("ENAME", tb.ename), XMLELEMENT("JOB", tb.job), XMLELEMENT("MGR", tb.mgr), 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 ) ,(SELECT XMLELEMENT("ATTR_VAL", XMLAGG(XMLELEMENT("EMP_ATTR",k.emp_attr, XMLELEMENT("VAL",k.val), XMLELEMENT("DATE_BEG",k.date_beg), XMLELEMENT("DATE_END",k.date_end) ))) FROM emp_attr_vals k WHERE k.empno = tb.empno --7369 ) )) ).GETCLOBVAL() INTO var_chk.var_emp_val FROM ( SELECT * FROM EMP WHERE emp.empno = cur.emp_no ) tb GROUP BY empno,mgr,job,hiredate,ename,deptno,comm; -- бизнес логика v_result := 0; 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; Проверим велосипед на ходу.
Проверять будем в два сеанса. Для контроля процесса в приглашение plus выведено текущее время, а в скобках sid текущего сеанса.
Тест 1. Ожидания на блокировок.
Сеанс 194695 открывает транзакцию.
20:43:48 zx(194695)@orcl> update emp set emp.comm = 100 where empno = 7566; 1 row updated. 20:50:02 zx(194695)@orcl>При незакрытой транзакции сеанса 194695 сеанс 194697 открывает и фиксирует новую транзакцию.
20:50:22 zx(194697)@orcl> update emp set emp.comm = 100 where empno = 7708; 1 row updated. 20:50:53 zx(194697)@orcl> commit; Commit complete. 20:50:59 zx(194697)@orcl>Сеанс 194695 успешно фиксирует свою транзакцию.
20:50:02 zx(194695)@orcl> commit; Commit complete. 20:51:12 zx(194695)@orcl> Тест 2. Ожидания на блокировках при попытке фиксации в одном из сеансов некорректной информации.
Сеанс 194695 открывает транзакцию.
21:25:43 zx(194695)@orcl> update emp set emp.comm = 200 where empno = 7566; 1 row updated. 21:25:48 zx(194695)@orcl>При незакрытой транзакции сеанса 194695 сеанс 194697 открывает транзакцию которая при попытке фиксации отвергается бизнесконстрейнтом.
21:25:58 zx(194697)@orcl> update EMP_ATTR_VALS set date_end = TO_DATE('12012016','ddmmyyyy') where EMPNO=7369 and EMP_ATTR ='SALARY' and DATE_BEG = TO_DATE('01012016','ddmmyyyy'); 1 row updated. 21:26:32 zx(194697)@orcl> 21:26:33 zx(194697)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: Ошибка. Пересечение дат в истории параметра SALARY ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 21:26:43 zx(194697)@orcl>Сеанс 194695 успешно фиксирует свою транзакцию.
21:25:50 zx(194695)@orcl> commit; Commit complete. 21:26:53 zx(194695)@orcl>Тест 3. Контроль отработки бизнесправила ограничения количества клерков в филиале.
21:46:50 zx(194695)@orcl> update emp set job = 'MANAGER' where empno = 7708; 1 row updated. 21:47:06 zx(194695)@orcl> commit; Commit complete. 21:47:11 zx(194695)@orcl> update emp set job = 'CLERK' where empno = 7708; 1 row updated. 21:47:32 zx(194695)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: Ошибка. В филиале DALLAS больше 2х клерков ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 21:47:37 zx(194695)@orcl>Тест 4. Отработка бизнесправила контроля размера зарплаты.
22:29:29 zx(194695)@orcl> update emp_attr_vals eav set eav.val='30000' where EAV.EMP_ATTR='SALARY' and sysdate BETWEEN eav.date_beg and EAV.DATE_END and eav.empno = 7777; 1 row updated. 22:29:36 zx(194695)@orcl> commit; Commit complete. 22:29:42 zx(194695)@orcl> update emp_attr_vals eav set eav.val='2000' where EAV.EMP_ATTR='SALARY' and sysdate BETWEEN eav.date_beg and EAV.DATE_END and eav.empno = 7708; 1 row updated. 22:30:27 zx(194695)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: Ошибка. Только президент может иметь зарплату больше 999 ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 22:30:32 zx(194695)@orcl>Что и требовалось доказать. Естественно речь идет о размере зарплаты что к констрейнту отношения не имеет ).
ссылка на оригинал статьи https://habrahabr.ru/post/282335/
Добавить комментарий