Difficult integrity constraint
Once it became necessary to organize a complex control of logically related information entered simultaneously in several tables in the ORACLE database. The transformation of the initial logically correct data set in the tables into the final logically correct set is performed by a sequence of DML operators. In this case, the modification can be performed by an arbitrary client whose behavior is uncontrollable, and the data structure is such that during the execution of the step-by-step modification, at some steps the data set may become logically erroneous.
The simplest example is a table of the history of values of three fields: value, the starting date of the value action, the ending date of the value action. Logically correct history can not have records that overlap periods of validity values. To change the boundaries of the action of two adjacent values, you need to change two dates - the end date of the previous value in the previous entry and the start date of the next one in the next entry. If we move the boundary of the change of values in time forward and in the first step move the expiration date of the value of the first record forward, we obtain a logically erroneous data set. That is why it is impossible to solve the problem with tabular triggers - they work for each data modification operator.
The real problem is slightly different from the simplest example. The data set is decomposed into a dozen tables, the business control rules algorithm has resulted in a 400-line procedure with access to API on other servers via links.
')
To implement such control, a trigger was needed, which is triggered only once in a transaction for a COMMIT event, with the possibility of rolling back the transaction based on the result of the development of the business logic control procedure. Such a trigger was found.
where MV is:
Consider the example of implementation details. Data set.
The example data is a set of entities of the type - “Employee” with information about the division and location of the division. Let's try to implement a business rule for this data limiting the number of employees with a 'CLERK' position in one city not more than 2x.
In the general case, there may be several rules of business control, and in a single transaction, the information of several employees is modified. Accordingly, at the time of commit, we need to have two sets of information:
- the set of fields that have been modified will determine the list of business rules that should be controlled;
- a set of identifiers of employees to be monitored.
A practical list of business control rules and their complexity allow, without a critical load on the server, to check each modified employee for all the implemented rules. This assumption will allow in our case to simplify the implementation of integrity constraints.
Create a table that will contain a set of employee IDs modified by the current transaction.
On all tables containing information for the control rule, we hang the trigger with which we will insert the identifiers of the modified employees into emp_chk. Some comments on triggers. The customer of the combat use of the control functionality demanded compatibility with ORACLE-9, therefore the trigger is not compound.
The ability to disable the construst is implemented by the var_chk.chk_on batch function. The use of the function 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). Full package text will be provided later.
The use of MERGE is caused by the desire to carry out a modification with one operator. The emp_chk.i field is the charge for using MERGE since write MERGE without the phrase WHEN MATCHED failed.
The following triggers clean up the emp_chk table at the beginning of a new transaction. The batch variable var_chk.first_dml_in_commit manages cleaning:
Create a materialized view.
The trigger initializing var_chk.first_dml_in_commit ensures that EMP_CHK is cleared at the start of a transaction.
Actually trigger triggering business control.
Some comments on the text CHK_EMP_MV_AS. Debugging and control of the functioning of the framework can be facilitated by logging. Let's consider that in case of an error, the data set presented for commit usually rolls back and is lost. In this implementation, not only the final processing status is written to the log, but also the entire data set of the employee who has undergone the modification presented to commit-a regardless of the processing result. Snapshots of datasets are placed in the emp_chk_log.XML field. The log is written with the var_chk.write_log package function into a 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 in the development and maintenance of business rules of the force and the layer of the system mechanisms of its operation. Below is the var_chk package text.
The function of controlling business rules.
Check the bike on the go.
The real implementation of this solution has been working on three dozen servers of the central office and branches since spring 2015.
The simplest example is a table of the history of values of three fields: value, the starting date of the value action, the ending date of the value action. Logically correct history can not have records that overlap periods of validity values. To change the boundaries of the action of two adjacent values, you need to change two dates - the end date of the previous value in the previous entry and the start date of the next one in the next entry. If we move the boundary of the change of values in time forward and in the first step move the expiration date of the value of the first record forward, we obtain a logically erroneous data set. That is why it is impossible to solve the problem with tabular triggers - they work for each data modification operator.
The real problem is slightly different from the simplest example. The data set is decomposed into a dozen tables, the business control rules algorithm has resulted in a 400-line procedure with access to API on other servers via links.
')
To implement such control, a trigger was needed, which is triggered only once in a transaction for a COMMIT event, with the possibility of rolling back the transaction based on the result of the development of the business logic control procedure. Such a trigger was found.
CREATE OR REPLACE TRIGGER <mv_as> AFTER DELETE OR INSERT OR UPDATE ON MV where MV is:
CREATE MATERIALIZED VIEW MV REFRESH COMPLETE ON COMMIT AS SELECT <,,,,> FROM <tab>; Consider the example of implementation details. Data set.
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; The example data is a set of entities of the type - “Employee” with information about the division and location of the division. Let's try to implement a business rule for this data limiting the number of employees with a 'CLERK' position in one city not more than 2x.
In the general case, there may be several rules of business control, and in a single transaction, the information of several employees is modified. Accordingly, at the time of commit, we need to have two sets of information:
- the set of fields that have been modified will determine the list of business rules that should be controlled;
- a set of identifiers of employees to be monitored.
A practical list of business control rules and their complexity allow, without a critical load on the server, to check each modified employee for all the implemented rules. This assumption will allow in our case to simplify the implementation of integrity constraints.
Create a table that will contain a set of employee IDs modified by the current transaction.
create table emp_chk ( emp_no NUMBER, i NUMBER); alter table emp_chk add constraint PK_emp_no primary key (emp_no); On all tables containing information for the control rule, we hang the trigger with which we will insert the identifiers of the modified employees into emp_chk. Some comments on triggers. The customer of the combat use of the control functionality demanded compatibility with ORACLE-9, therefore the trigger is not compound.
The ability to disable the construst is implemented by the var_chk.chk_on batch function. The use of the function 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). Full package text will be provided later.
The use of MERGE is caused by the desire to carry out a modification with one operator. The emp_chk.i field is the charge for using MERGE since write MERGE without the phrase WHEN MATCHED failed.
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, ai) VALUES (b.emp_no, bi) WHEN MATCHED THEN UPDATE SET ai = bi; 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, ai) VALUES (b.emp_no, bi) WHEN MATCHED THEN UPDATE SET ai = bi; END dept_chk_ar; The following triggers clean up the emp_chk table at the beginning of a new transaction. The batch variable var_chk.first_dml_in_commit manages cleaning:
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; Create a materialized view.
CREATE MATERIALIZED VIEW chk_emp_mv REFRESH COMPLETE ON COMMIT AS SELECT 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 a transaction.
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; Actually trigger triggering business control.
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; Some comments on the text CHK_EMP_MV_AS. Debugging and control of the functioning of the framework can be facilitated by logging. Let's consider that in case of an error, the data set presented for commit usually rolls back and is lost. In this implementation, not only the final processing status is written to the log, but also the entire data set of the employee who has undergone the modification presented to commit-a regardless of the processing result. Snapshots of datasets are placed in the emp_chk_log.XML field. The log is written with the var_chk.write_log package function into a table:
create table emp_chk_log ( ts DATE, status NUMBER, XML CLOB ); 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 in the development and maintenance of business rules of the force and the layer of the system mechanisms of its operation. Below is the var_chk package text.
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; The function of controlling business rules.
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; 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-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> The real implementation of this solution has been working on three dozen servers of the central office and branches since spring 2015.
Source: https://habr.com/ru/post/280834/
All Articles