Простейший пример это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.
Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 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 года.
Комментарии (17)
teleavtomatika
04.04.2016 15:01-2А я думал, что люди, пишущие бизнес логику в БД уже не существуют. Как вы с этим потом живете? Миграции, отсутствие нормальных средств тестирования. Я как-то видел базу в которой десятки тысяч строк кода. Это был ад.
BalinTomsk
04.04.2016 19:03+4---А я думал, что люди, пишущие бизнес логику в БД уже не существуют.
Мне приходилось видеть людей, которые загружали данные всей таблицы запросом select * from tbl, чтобы подсчитать количество строк или сумму значений по какому-то полю.
----отсутствие нормальных средств тестирования
юнит-тестирование логики не сильно отличается от других языков.
— Я как-то видел базу в которой десятки тысяч строк кода. Это был ад
Я бы сказал что это небольшая база, в SAP к примеру одних только таблиц десятки тысяч.teleavtomatika
05.04.2016 02:48Мне приходилось видеть людей, которые загружали данные всей таблицы запросом select * from tbl, чтобы подсчитать количество строк или сумму значений по какому-то полю.
Юнцов хватает в любой сфере деятельности человека. Но здесь то речь идет о явно опытном специалисте. И он пишет правильные вещи, только на мой взгляд в не правильном месте. На сколько я понимаю текущие тенденции, все идет к тому, чтобы использовать БД в качестве массового хранилища данных с минимальным количеством логики внутри (обычно оставляют только минимальные constraint'ы) – это проще, лучше обслуживается, легче масштабируется.
юнит-тестирование логики не сильно отличается от других языков.
Я слабо верю в то, что тесты для моего кода на С# будут столь же затратны по времени как и тесты для SQL написаннные на SQL.
Я бы сказал что это небольшая база, в SAP к примеру одних только таблиц десятки тысяч.
Я писал о том, что в базе было много кода, а не много таблиц. Количеством таблиц меня не удивить. Меня пугает, когда эти таблицы живут своей жизнью, связаны кучей триггеров и используют сотни хранимых процедур для отработки какой-то логики внутри БД.
xtender
05.04.2016 05:02Обеспечивать полный ACID в коде промежуточного слоя весьма тяжело и затратно, особенно в больших многопользовательских распределенных системах, так не лучше ли делать то, что умеет RDBMS быстро и эффективно прямо там?
teleavtomatika
05.04.2016 10:44А транзакции и ограничения уникальности уже кто-то отменил? Или они не работают если их делать в коде на стороне службы, а не БД?
xtender
05.04.2016 05:05Попробуйте реализовать хотя бы ту задачу, что делал автор поста, сделать на апп.сервере с учетом многопользовательской системы и все сами поймете.
teleavtomatika
05.04.2016 10:55Я их уже не один десяток подобных проделал. Мне вполне хватает возможностей ORM для этих целей.
xtender
05.04.2016 14:06Три разных пользователя вставляют клерков в один отдел одновременн — ваши действия?
teleavtomatika
06.04.2016 02:23Слишком мало исходных данных. Ну допустим:
1. Все запросы обрабатывает центральная служба.
2. Все запросы службы к БД выполняются в одном потоке.
3. Тогда я перед вставкой клерка в БД считаю количество уже существующих клерков и не даю вставлять если их больше чем нужно.
4. Т.к. запросы сериализованы – первый пройдет, остальные обломятся.
mamonm
05.04.2016 00:48Общие замечания.
Возраст реального комплекса полтора десятка лет — поэтому речь идет не столько о разработке космических кораблей сколько о ремонте и поддержке любимых велосипедов. Данные примера на которые накручен функционал в статье так-же соотносятся с реальными как соотносятся морская свинка и море )
Теперь серьезнее.
Предложение вынести бизнеслогику я бы сформулировал иначе. Смысл не в том в каком месте физически реализована бизнеслогика, а в том что бизнеслогика и функционал системной поддержи должны реализовываться в отдельных модулях. Эта попытка сделана (наверно несовременно) — вся бизнеслогика реализована в отдельной функции. Исполнитель поддерживающий эту функцию имеет очень поверхностные представления о системном слое вызова и отлаживает ее простым вызовом передав идентификатор экзэмляра проверяемой сущности. При этом текст с парой операторов в триггерах (по сравнению с текстом функции контроля) словом логика даже называть неудобно.
О массовых многострочных операциях.
При одном коммите "на нескольких миллионах строк" по десятку таблиц серверу с OLTP нагрузкой будет весело и без констрейнта.
Но говорят дбашникам никогда не надоедает смотреть на воду, огонь, кокуренцию на блокировках, и рост сегментов отката )
А когда надоедает они отключают констрейнт функцией var_chk.chk_on, которая в примере реализована как заглушка.
2teleavtomatika Предкомпилированый код PLSQL хранящийся на сервере вроде не самое плохое решение по производительности. Ошибаюсь? Серверов БД с… тысяч сток кода ну очень много — достаточно заглянуть в схемы SYSTEM и SYS.
2Ariant. Вас просто не понял. Предложите Ваше решение контроля информации одной строки по содержанию другой — выберем лучшее).
2sassy. Матвью не самоцель — инструмент. Цель-инициировать процесс котроля только по коммиту.
По VIEW и INSTEAD OF триггера — спасибо, посмотрю подумаю.
Извините повторюсь. Решение не идеально но оно реально работает.xtender
05.04.2016 04:50+1Ну во-первых подход древний(кажется еще Кайт его описывал и уже тогда предлагал вариант получше — с mview log+fast refresh) и с кучей недостатков, например, в сложности зависимостей, громоздкости кода, которая провоцирует на трудноуловимые ошибки, ужасная производительность, но даже в этом древнем подходе такую тяжелую жесть обычно не создают. Учтите, что вы таким подходом фактически превращаете систему в однопользовательскую — два пользователя не смогут одновременно поменять хоть что-то у двух разных сотрудников в разных департаментах, т.к. их коммиты будут ждать друг друга из-за блокировок на MVIEW, да и кол-во генерируемого реду будет просто зашкаливать при мельчайших изменениях… И кстати, подход Кайта в вашем примере будет намного проще — просто fast refresh mview c count(*) по клеркам в разрезе департаментов с check constraint (cnt<=2).
Во-вторых, обычно в таких случаях предпочитают не такую размазанную и тяжелую логику с кучей слабых мест, а уменьшение точек входа — в идеале одной процедуры, которая вносит изменения и вызывает процедуры проверок бизнес-логики. Имхо главные правила — блокируй как можно меньше, не создавай бутылочных горлышек, и конечно принцип KISS/бритва Оккама ...mamonm
06.04.2016 00:38Ну во-первых подход древний(кажется еще Кайт его описывал и уже тогда предлагал вариант получше — с mview log+fast refresh)
Предложеный мною вариант не претендует на новизну. Скорее это пример практически реализованного на двух десятках серверов решения прошедшего годовую обкатку под нагрузкой OLTP до 300 коннектов и пока не приведшего к ужасам описаными Вами ниже.
Однако должен уточнить некоторые условия эксплуатации. Данный констрейнт по сути эмулирует для пакетного режима ввода интерактивный режим ввода через форму на клиенте. Форма толстого клиента именно так и работает — бизнеслогика зашитая в ней обеспечивает формирование на полях корректного набора данных и лиш потом позволяет запостить и закомитить данные на сервере. Реальная реализация констрейнта динамически включена только для сеансов пользователей под которыми работают внешние программные шлюзы (функция пакета chk_on). Для интерактивных сеансов толстого клиента триггера и матвьюха отдыхают а конкуренции на матвьюхе отсутствует.
Наверно в основном посте я сосредоточился на описании инструмента опустив технологию применения — похоже напрасно. Инструмент не претендует на всеобщую универсальность но свою задачу решает.
их коммиты будут ждать друг друга из-за блокировок на MVIEW — если включить констрейнт для всех сеансов — да, полностью согласен. Но поправимо. Поскольку блокировки ставятся построчно доработаем так, что бы каждый сеанс работал только со своими строками. Добавим в emp_chk и матвью два поля идентификаторов сеанса (sid/serial). Соответсвенно протянуть обработку этих полей во фразе where через все модули. Спасибо за идею )
И кстати, подход Кайта в вашем примере Извините. Смысл был в том чтоб описать архитектуру решения на простом примере. Вроде есть самосвал в который грузят ведро грунта для примера. В результате половина постов смотрят не на самосвал а на ведро грута и предлагают переделать самосвал в велосипед с прицепом потому как для ведра грунта достаточно велосипеда (извините за безобразное упрощенчество). Продолжаю повторять что с моей точки зрения решение легко масштабируется тк исходники триггеров 2х типов по сути являются шаблонами для установки которых на любую таблицу необходимы минимальные правки исходника. И если Вы таки встречали сложную бизнеслогику (см выше )) на форме то согласитесь что 200 строк из 4х шаблонов кода как плата за перенос ее из клиента на сервер не высока.
sassy
05.04.2016 00:49+3Создавать матвью только для проверки целостности данных довольно спорное решение. Если вопрос в изменении нескольких записей так сказать в бэк-енде, то лучше использовать VIEW и INSTEAD OF триггера над этими VIEW. Выполняете изменение одной записи в VIEW, а триггером правите сколько угодно записей в таблице (таблицах) за этой вью.
А такая матвью на нескольких миллионах строк будет создавать излишние накладные расходы при коммите.mamonm
05.04.2016 23:03Всетаки задача предложеного иструмента конкретна — допускать к фиксации только прошедшие бизнеслогику данные. Задача доопределять данные инициируя дополнительные DML не стоияла.
Похоже INSTEAD OF триггера работают по каждому DML по полям вьюхи — тогда возражаю. Функционал должен работать только по конечному набору данных.
Любой функционал приводит к расходам. Если функционал лишний — расходы тоже излишние.
sparhawk
Откладывание проверки до commit — очень плохая практика для последующего переиспользования кода. Сложно будет делать массовые операции или подключать приложения, которые самостоятельно управляют транзакциями (exception в необычном месте).
И слишком громоздко для проверки "единственности" клерка в филиале. Если это просто пример одной проверки, а реальные проверки гораздо сложнее, то сойдет.
Если делаются только проверки, похожие на обычный constraint, то их конечно, лучше реализовывать с помощью constraint'ов. В данном примере — сделав денормализацию поля LOC (филиал) в таблице EMP и добавив проверку уникальности сочетания LOC, IS_CLERK. Плюс этого решения — крайняя простота (и сработает даже в Oracle 9i), минус — проблемы при массовых изменениях DEPT.LOC.