Однажды возникла необходимость организовать сложный контроль вводимой одновременно в несколько таблиц логически связанной информации в БД ORACLE. Преобразование начального логически корректного набора данных в таблицах в конечный логически корректный набор производится последовательностью DML операторов. При этом проводить модификацию может произвольный клиент поведение которого неконтролируемо а структура данных такова, что в процессе выполнения пошаговой модификации на некоторых шагах набор данных может становится логически ошибочным.
Простейший пример это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.
Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 400 строк с обращением через линки к API на других серверах.
Для реализации такого контроля нужен был триггер, который срабатывает только один раз в транзакции по событию COMMIT, с возможностью отката транзакции по результату отработки процедуры контроля бизнес логики. Такой триггер нашелся.
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; Данные примера является набором сущностей типа — «Сотрудник» с информацией о подразделении и месте расположения подразделения. Попробуем реализовать для этих данных бизнес правило ограничивающее количество сотрудников с должностью ‘CLERK’ в одном городе не более 2х.
В общем случае правил бизнес контроля может быть несколько а в одной транзакции проходить модификация информации нескольких сотрудников. Соответственно на момент commit-а нам надо иметь два набора информации:
— набор полей которые были модифицированы определит список бизнес правил которые должны быть проконтролированы;
— набор идентификаторов сотрудников подлежащих контролю.
Практический список правил бизнес контроля и их сложность позволяют без критической нагрузки на сервер осуществлять проверку каждого модифицированного сотрудника по всем реализованным правилам. Это допущение позволит в нашем случае упростить реализацию ограничения целостности.
Создаем таблицу которая будет содержать набор идентификаторов сотрудников модифицированных текущей транзакцией.
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, поэтому триггера не составные (compound).
Возможность отключения констрэйнта реализована пакетной функцией var_chk.chk_on. Использование для этой цели функции дает возможность управлять контролем не только статически (через конфигурационную таблицу) но и динамически (например для разных сеансов БД). Полный текст пакета будет приведен позже.
Использование MERGE вызвано желанием проводить модификацию одним оператором. Поле emp_chk.i является платой за использование MERGE т.к. написать MERGE без фразы WHEN MATCHED не удалось.
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, a.i) VALUES (b.emp_no, b.i) WHEN MATCHED THEN UPDATE SET a.i = b.i; 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, a.i) VALUES (b.emp_no, b.i) WHEN MATCHED THEN UPDATE SET a.i = b.i; 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; Создаем materialized view.
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-а обычно откатывается и теряется. В данной реализации в лог пишется не только итоговый статус обработки но и весь набор данных сотрудника подвергшегося модификации предъявленный для 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 года.
ссылка на оригинал статьи https://habrahabr.ru/post/280834/
Добавить комментарий