[Из песочницы] Сложное ограничение целостности (constraint)
Однажды возникла необходимость организовать сложный контроль вводимой одновременно в несколько таблиц логически связанной информации в БД ORACLE. Преобразование начального логически корректного набора данных в таблицах в конечный логически корректный набор производится последовательностью DML операторов. При этом проводить модификацию может произвольный клиент поведение которого неконтролируемо, а структура данных такова, что в процессе выполнения пошаговой модификации на некоторых шагах набор данных может становится логически ошибочным.
Простейший пример это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.
Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 400 строк с обращением через линки к API на других серверах.
Для реализации такого контроля нужен был триггер, который срабатывает только один раз в транзакции по событию COMMIT, с возможностью отката транзакции по результату отработки процедуры контроля бизнес логики. Такой триггер нашелся.
CREATE OR REPLACE TRIGGER
AFTER DELETE OR INSERT OR UPDATE ON MV
где MV — это:
CREATE MATERIALIZED VIEW MV
REFRESH COMPLETE ON COMMIT
AS SELECT <,,,,> FROM ;
Рассмотрим на примере подробности реализации. Набор данных.
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
7708SCOTT CLERK 7566 300020 09.12.1982 2
0RESEARCH DALLAS 7369SMITH CLERK 7902 280020 17.12.1980 2
0RESEARCH DALLAS
Реальная реализация этого решения работает на трех десятках серверов центрального офиса и филиалов с весны 2015 года.
