Complex constraint constraint
Once there was a need to organize complex control of logically related information entered into several tables simultaneously in the ORACLE database.The conversion of the initial logically correct data set in the tables to the final logically correct data set is performed by a sequence of DML operators. At the same time, an arbitrary client can conduct the modification, whose behavior is uncontrollable and the data structure is such that, in the process of performing a step-by-step modification, at some steps the data set may become logically erroneous.
The simplest example is a history table of values from three fields: value, the start date of the value, the end date of the value. Logically correct history cannot have records at which periods of validity of values overlap. To change the action boundary of two adjacent values, two dates must be changed - the end date of the previous value in the previous record and the start date of the next value in the next record. If we move the frontier of the change of values in time forward and in the first step rearrange the expiration date of the value of the first record, we get a logically erroneous data set. That is why it is impossible to solve the problem with table triggers - they work for each data modification operator.
The real task is slightly different from the simplest example. The data set is laid out in a dozen tables, the business control rules algorithm resulted in a 400-line procedure with access to APIs on other servers via links.
To implement such control, a trigger was needed that fires only once in a transaction on a COMMIT event, with the ability to roll back the transaction as a result of working out the business logic control procedure. Such a trigger was found.
where MV is:
Consider the implementation details as an example. Data set.
Example data is a set of entities of the type - "Employee" with information about the unit and the location of the unit. Let's try to implement a business rule for this data limiting the number of employees with a position of 'CLERK' in one city no more than 2.
In the general case, there may be several business control rules, and in one transaction, information of several employees can be modified. Accordingly, at the time of commit, we need to have two sets of information:
- a set of fields that have been modified will determine a list of business rules that should be monitored;
- a set of identifiers of employees subject to control.
A practical list of business control rules and their complexity allow, without a critical load on the server, to check each modified employee according to all implemented rules. This assumption will allow in our case to simplify the implementation of integrity constraints.
We create a table that will contain a set of identifiers for employees modified by the current transaction.
On all tables containing information for the control rule, we set up a trigger with which we will insert the identifiers of modified employees in emp_chk. Some comments on triggers. The customer of the combat use of the control functionality required compatibility with ORACLE-9, so the triggers are not compound.
The ability to disable the construct is implemented by the batch function var_chk.chk_on. Using functions for this purpose makes it possible to control the control not only statically (via the configuration table) but also dynamically (for example, for different database sessions). The full text of the package will be given later.
The use of MERGE is caused by the desire to carry out a modification by one operator. The emp_chk.i field is a fee for using MERGE since write MERGE without the phrase WHEN MATCHED failed.
The emp_chk table is cleared at the start of a new transaction by the following triggers. The batch variable var_chk.first_dml_in_commit controls the cleanup:
Create a materialized view.
The trigger initializing var_chk.first_dml_in_commit ensures that EMP_CHK is cleared at the start of the transaction.
Actually, the trigger is launching business control.
Some comments on the text CHK_EMP_MV_AS. Debugging and monitoring the functioning of the construct can be facilitated by logging. Consider that in case of an error the data set presented for commit is usually rolled back and lost. In this implementation, not only the final processing status is written to the log, but also the entire data set of the employee of the modified version presented for commit-a regardless of the processing result. Snapshots of datasets are placed in the emp_chk_log.XML field. The log is written by the batch function var_chk.write_log into the table:
All business rules are implemented in a separate emp_logic function. The function is not a member of the package. This allows us to separate the design rules and the layer of system mechanisms of its functioning in the development and maintenance of business. Below is the text of the var_chk package.
The function of controlling business rules.
Check the bike on the go.
The actual implementation of this solution has been running on three dozen central office servers and branches since the spring of 2015.
The simplest example is a history table of values from three fields: value, the start date of the value, the end date of the value. Logically correct history cannot have records at which periods of validity of values overlap. To change the action boundary of two adjacent values, two dates must be changed - the end date of the previous value in the previous record and the start date of the next value in the next record. If we move the frontier of the change of values in time forward and in the first step rearrange the expiration date of the value of the first record, we get a logically erroneous data set. That is why it is impossible to solve the problem with table triggers - they work for each data modification operator.
The real task is slightly different from the simplest example. The data set is laid out in a dozen tables, the business control rules algorithm resulted in a 400-line procedure with access to APIs on other servers via links.
To implement such control, a trigger was needed that fires only once in a transaction on a COMMIT event, with the ability to roll back the transaction as a result of working out the business logic control procedure. Such a trigger was found.
CREATEORREPLACETRIGGER <mv_as>
AFTERDELETEORINSERTORUPDATEON MV
where MV is:
CREATEMATERIALIZEDVIEW MV
REFRESHCOMPLETEONCOMMITASSELECT <,,,,> FROM <tab>;
Consider the implementation details as an example. Data set.
CREATETABLE EMP
(EMPNO NUMBER(4) NOTNULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(17,2),
DEPTNO NUMBER(2));
CREATETABLE DEPT
(DEPTNO NUMBER(2) NOTNULL,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
insertinto DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insertinto DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insertinto DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insertinto DEPT (DEPTNO, DNAME, LOC) values (31, 'OPERATIONS', 'CHICAGO');
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, TIMESTAMP'1980-12-17 00:00:00', 2800, null, 20);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, TIMESTAMP'1981-02-20 00:00:00', 1600, 300, 31);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, TIMESTAMP'1981-02-22 00:00:00', 1250, 500, 30);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, TIMESTAMP'1981-04-02 00:00:00', 2975, null, 20);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7650, 'MARTIN', 'SALESMAN', 7698, TIMESTAMP'1981-09-28 00:00:00', 1251, 1400, 30);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, TIMESTAMP'1981-05-01 00:00:00', 2850, null, 30);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, TIMESTAMP'1981-06-09 00:00:00', 2450, null, 10);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7708, 'SCOTT', 'ANALYST', 7566, TIMESTAMP'1982-12-09 00:00:00', 3000, null, 20);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7639, 'KING', 'PRESIDENT', null, TIMESTAMP'1981-11-17 00:00:00', 5000, null, 10);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, TIMESTAMP'1981-09-10 00:00:00', 1500, 0, 30);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, TIMESTAMP'1982-01-12 00:00:00', 1100, null, 20);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, TIMESTAMP'1981-12-03 00:00:00', 950, null, 31);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, TIMESTAMP'1981-12-03 00:00:00', 3000, null, 20);
insertinto EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, TIMESTAMP'1982-01-23 00:00:00', 1300, null, 10);
altertable emp addconstraint еmр_рk primary key(empno);
altertable dept addconstraint dept_pk primary key(deptno);
altertable emp addconstraint emp_fk_dept foreignkey (deptno) references dept;
Example data is a set of entities of the type - "Employee" with information about the unit and the location of the unit. Let's try to implement a business rule for this data limiting the number of employees with a position of 'CLERK' in one city no more than 2.
In the general case, there may be several business control rules, and in one transaction, information of several employees can be modified. Accordingly, at the time of commit, we need to have two sets of information:
- a set of fields that have been modified will determine a list of business rules that should be monitored;
- a set of identifiers of employees subject to control.
A practical list of business control rules and their complexity allow, without a critical load on the server, to check each modified employee according to all implemented rules. This assumption will allow in our case to simplify the implementation of integrity constraints.
We create a table that will contain a set of identifiers for employees modified by the current transaction.
createtable emp_chk ( emp_no NUMBER,
i NUMBER);
altertable emp_chk addconstraint PK_emp_no primary key (emp_no);
On all tables containing information for the control rule, we set up a trigger with which we will insert the identifiers of modified employees in emp_chk. Some comments on triggers. The customer of the combat use of the control functionality required compatibility with ORACLE-9, so the triggers are not compound.
The ability to disable the construct is implemented by the batch function var_chk.chk_on. Using functions for this purpose makes it possible to control the control not only statically (via the configuration table) but also dynamically (for example, for different database sessions). The full text of the package will be given later.
The use of MERGE is caused by the desire to carry out a modification by one operator. The emp_chk.i field is a fee for using MERGE since write MERGE without the phrase WHEN MATCHED failed.
CREATEORREPLACETRIGGER emp_chk_ar
AFTERDELETEORINSERTORUPDATEON emp
FOREACHROWBEGINIF var_chk.chk_on != 1THENRETURN;
ENDIF;
MERGEINTO emp_chk a
USING (SELECT nvl(:new.empno, :old.empno) AS emp_no
, 1AS i
FROM dual ) b
ON (a.emp_no = b.emp_no)
WHENNOTMATCHEDTHENINSERT (a.emp_no, a.i) VALUES (b.emp_no, b.i)
WHENMATCHEDTHENUPDATESET a.i = b.i;
END emp_chk_ar;
CREATEORREPLACETRIGGER dept_chk_ar
AFTERDELETEORINSERTORUPDATEON dept
FOREACHROWBEGINIF var_chk.chk_on != 1THENRETURN;
ENDIF;
MERGEINTO emp_chk a
USING (
SELECT emp.empno AS emp_no , 1AS i
FROM emp
WHERE emp.deptno = NVL(:new.deptno, :old.deptno)
) b
ON (a.emp_no = b.emp_no)
WHENNOTMATCHEDTHENINSERT (a.emp_no, a.i) VALUES (b.emp_no, b.i)
WHENMATCHEDTHENUPDATESET a.i = b.i;
END dept_chk_ar;
The emp_chk table is cleared at the start of a new transaction by the following triggers. The batch variable var_chk.first_dml_in_commit controls the cleanup:
CREATEORREPLACETRIGGER emp_chk_bs
BEFOREDELETEORINSERTORUPDATEON emp
BEGINIF var_chk.chk_on != 1THENRETURN;
ENDIF;
IF var_chk.first_dml_in_commit = 1 THEN
DELETEFROM emp_chk;
ENDIF;
var_chk.first_dml_in_commit := 0 ;
END emp_chk_bs;
CREATEORREPLACETRIGGER dept_chk_bs
BEFOREDELETEORINSERTORUPDATEON dept
BEGINIF var_chk.chk_on != 1THENRETURN;
ENDIF;
IF var_chk.first_dml_in_commit = 1 THEN
DELETEFROM emp_chk;
ENDIF;
var_chk.first_dml_in_commit := 0;
END dept_chk_bs;
Create a materialized view.
CREATEMATERIALIZEDVIEW chk_emp_mv
REFRESHCOMPLETEONCOMMITASSELECT emp_no,i FROM emp_chk;
The trigger initializing var_chk.first_dml_in_commit ensures that EMP_CHK is cleared at the start of the transaction.
CREATEORREPLACETRIGGER chk_emp_mv_bs
BEFOREDELETEORINSERTORUPDATEON chk_emp_mv
BEGINIF var_chk.chk_on != 1THENRETURN;
ENDIF;
var_chk.first_dml_in_commit := 1 ;
END chk_emp_mv_bs;
Actually, the trigger is launching business control.
CREATEORREPLACETRIGGER chk_emp_mv_as
AFTERDELETEORINSERTORUPDATEON chk_emp_mv
DECLARE
v_result NUMBER;
v_errtxt VARCHAR2(512);
BEGINIF var_chk.chk_on != 1THENRETURN;
ENDIF;
FOR cur IN (SELECT t.emp_no FROM CHK_EMP_MV t) LOOPSELECTXMLELEMENT("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'))
,(SELECTXMLELEMENT("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
GROUPBY 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);
ENDIF;
ENDLOOP;
END chk_emp_mv_as;
Some comments on the text CHK_EMP_MV_AS. Debugging and monitoring the functioning of the construct can be facilitated by logging. Consider that in case of an error the data set presented for commit is usually rolled back and lost. In this implementation, not only the final processing status is written to the log, but also the entire data set of the employee of the modified version presented for commit-a regardless of the processing result. Snapshots of datasets are placed in the emp_chk_log.XML field. The log is written by the batch function var_chk.write_log into the table:
createtable emp_chk_log
( ts DATE,
statusNUMBER,
XMLCLOB
);
All business rules are implemented in a separate emp_logic function. The function is not a member of the package. This allows us to separate the design rules and the layer of system mechanisms of its functioning in the development and maintenance of business. Below is the text of the var_chk package.
CREATEORREPLACEPACKAGE 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;
CREATEORREPLACEPACKAGEBODY var_chk
AS--------------------FUNCTION chk_on RETURNNUMBERIS-- Возвращает 1 - контроль включен-- 0 - контроль выключен BEGINRETURN1;
END chk_on;
---------------------
PROCEDURE write_log (p_status NUMBER
,p_err_txt VARCHAR2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGININSERTINTO 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;
The function of controlling business rules.
CREATEORREPLACEFUNCTION emp_logic
(p_emp_no NUMBER
,p_errtxt OUT VARCHAR2 )
RETURNNUMBERIS
v_emp_count NUMBER;
v_emp_loc dept.loc%TYPE;
BEGINSELECT 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
GROUPBY dept.loc
;
IF v_emp_count > 2
THEN p_errtxt:='Ошибка:В филиале '||v_emp_loc||' больше 2х клерков';
RETURN 1;
ENDIF;
RETURN 0;
END emp_logic;
Check the bike on the go.
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-161
<EMP><EMPNO>7708<ENAME>SCOTT</ENAME><JOB>CLERK</JOB><MGR>7566</MGR><SAL>3000</SAL><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-160
<EMP><EMPNO>7369<ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><SAL>2800</SAL><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>
The actual implementation of this solution has been running on three dozen central office servers and branches since the spring of 2015.