В моей работе необходимость организовать сложный контроль вводимой одновременно в несколько таблиц логически связанной информации в БД ORACLE. Преобразование начального логически корректного набора данных в таблицах в конечный логически корректный набор производится последовательностью DML операторов. При этом проводить модификацию может произвольный клиент поведение которого неконтролируемо а структура данных такова, что в процессе выполнения пошаговой модификации на некоторых шагах набор данных может становится логически ошибочным.
Самый простой пример — это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.
Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 400 строк с обращением через линки к API на других серверах.
Для реализации такого контроля нужен был триггер, который срабатывает только один раз в транзакции по событию COMMIT, с возможностью отката транзакции по результату отработки процедуры контроля бизнес логики. Такой триггер нашелся.
где MV — это:
Рассмотрим на примере подробности реализации. Набор данных.
Данные примера является набором сущностей типа — «Сотрудник» с информацией о подразделении и месте расположения подразделения. Попробуем реализовать для этих данных бизнес правило ограничивающее количество сотрудников с должностью 'CLERK' в одном городе не более 2х.
В общем случае правил бизнес контроля может быть несколько а в одной транзакции проходить модификация информации нескольких сотрудников. Соответственно на момент commit-а нам надо иметь два набора информации:
— набор полей которые были модифицированы определит список бизнес правил которые должны быть проконтролированы;
— набор идентификаторов сотрудников подлежащих контролю.
Практический список правил бизнес контроля и их сложность позволяют без критической нагрузки на сервер осуществлять проверку каждого модифицированного сотрудника по всем реализованным правилам. Это допущение позволит в нашем случае упростить реализацию ограничения целостности.
Создаем таблицу которая будет содержать набор идентификаторов сотрудников модифицированных текущей транзакцией.
На все таблицы содержащие информацию для правила контроля вешаем триггера которыми будем вставлять в emp_chk идентификаторы модифицированных сотрудников. Некоторые комментарии к триггерам. Заказчик боевого применения функционала контроля потребовал совместимость с ORACLE-9, поэтому триггера не составные (compound).
Возможность отключения констрэйнта реализована пакетной функцией var_chk.chk_on. Использование для этой цели функции дает возможность управлять контролем не только статически (через конфигурационную таблицу) но и динамически (например для разных сеансов БД). Полный текст пакета будет приведен позже.
Использование MERGE вызвано желанием проводить модификацию одним оператором. Поле emp_chk.i является платой за использование MERGE т.к. написать MERGE без фразы WHEN MATCHED не удалось.
Очистку таблицы emp_chk в начале новой транзакции осуществляют следующие триггера. Управляет очисткой пакетная переменная var_chk.first_dml_in_commit:
Создаем materialized view.
Триггер инициализирующий var_chk.first_dml_in_commit обеспечивает очистку EMP_CHK в начале транзакции.
Собственно триггер запускающий бизнес контроль.
Некоторые комментарии к тексту CHK_EMP_MV_AS. Отладка и контроль функционирования констрэйнта можно облегчить логированием. Учтем, что в случае возникновения ошибки набор данных предъявленный для commit-а обычно откатывается и теряется. В данной реализации в лог пишется не только итоговый статус обработки но и весь набор данных сотрудника подвергшегося модификации предъявленный для commit-a независимо от результата обработки. Снимки наборов данных помещаются в поле emp_chk_log.XML. Лог пишется пакетной функцией var_chk.write_log в таблицу:
Все бизнес правила реализованы в отдельной функции emp_logic. Функция не является членом пакета. Это позволяет разделить в разработке и сопровождении бизнес правила констрйнта и слой системных механизмов его функционирования. Ниже текст пакета var_chk.
Функция контроля бизнес правил.
Проверим работоспособность кода.
Реальная реализация этого решения работает на трех десятках серверов центрального офиса.
Самый простой пример — это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.
Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 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>
Реальная реализация этого решения работает на трех десятках серверов центрального офиса.
GlukKazan
Для чего повтор?
MaximChistov
для халявного инвайта по скопированной статье, конечно же) модеры могли бы проверить статью перед аппрувом на уникальность.
GlukKazan
Это печально