Difficult integrity constraint - 2
Constructive public comments on the post The complex integrity constraint (constraint) in which the implementation of the complex ORACLE database constraint was presented forced us to dive deeper into the topic. Moreover, the functionality is in combat operation and it would be ugly to leave roughness in it.
What's new.
Looks like we managed to provide a multiplayer mode. Dear xtender quite rightly noted this lack of a previous implementation.
Test case business rules (spherical elephant) on the test dataset (in vacuum) are complicated. A more complex example clearly reflects not the worst scalability of the solution - the text of the triggers on different tables is almost identical. Adding any number of new business rules without changing the composition of the participating tables (emp, dept, emp_attr_vals) will require modification of only the emp_logic function. Probably the gurus will be able to implement this version of the TomkK (fast refresh mview) but there is a limit to everything. Tom himself believes that when the task is not implemented with one sql (mview), it’s time to decide on plsql.
The proposed solution has a drawback - not run-in to production under load.
Implementation.
Business Information Tables.
')
Log table
To work with the transaction scheme c example (ZX) will need some access to the scheme SYS.
Base table matvyu.
Create a materialized view.
Multi-user mode is provided as follows. At the beginning of each transaction, from the base table with the var_chk.cls_emp_chk procedure, the completed transaction information is deleted line by line. Using an autonomous transaction ensures clean-up regardless of the outcome (commit / rollback) of the current transaction. In the previous version, the whole table was cleared and, consequently, the entire table was locked until the end of the current transaction.
For each DML, the identifier of the controlled entity (in our example, an employee) and the identifier of the current transaction are written to the base table with any of the business information tables.
Business control function.
Trigger on matvyu.
Starting to clear the data of the previous transaction from the base table by matvue.
Starting the function of business control and writing to the log.
Check the bike on the go.
We will check in two sessions. To control the process, the plus prompt displays the current time, and in the sid brackets of the current session.
Test 1. Waiting for locking.
Session 194695 opens a transaction.
With an open transaction session 194695 session 194697 opens and fixes a new transaction.
Session 194695 successfully commits its transaction.
Test 2. Waits on locks when attempting to commit incorrect information in one of the sessions.
Session 194695 opens a transaction.
With an open transaction of a session of 194695, a session of 194697 opens a transaction that, when attempted to commit, is rejected by the business relationship.
Session 194695 successfully commits its transaction.
Test 3. Controlling the business rules of limiting the number of clerks in the branch.
Test 4. Fulfillment of the business rule of wage control.
Q.E.D. Naturally, we are talking about the amount of salary that has nothing to do with the contrast).
What's new.
Looks like we managed to provide a multiplayer mode. Dear xtender quite rightly noted this lack of a previous implementation.
Test case business rules (spherical elephant) on the test dataset (in vacuum) are complicated. A more complex example clearly reflects not the worst scalability of the solution - the text of the triggers on different tables is almost identical. Adding any number of new business rules without changing the composition of the participating tables (emp, dept, emp_attr_vals) will require modification of only the emp_logic function. Probably the gurus will be able to implement this version of the TomkK (fast refresh mview) but there is a limit to everything. Tom himself believes that when the task is not implemented with one sql (mview), it’s time to decide on plsql.
The proposed solution has a drawback - not run-in to production under load.
Implementation.
Business Information Tables.
CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) ); CREATE UNIQUE INDEX "DEPT_PK" ON "DEPT" ("DEPTNO"); ALTER TABLE "DEPT" ADD CONSTRAINT "DEPT_PK" PRIMARY KEY ("DEPTNO") ENABLE; Data
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'); ')
CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "COMM" NUMBER(17,2), "DEPTNO" NUMBER(2,0) ); Data
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7369','SMITH','CLERK','7902',to_date('17.12.1980','DD.MM.RRRR '),'100','20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7499','ALLEN','SALESMAN','7698',to_date('20.02.1981','DD.MM.RRRR '),'300','31'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7521','WARD','SALESMAN','7698',to_date('22.02.1981','DD.MM.RRRR '),'10','30'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7566','JONES','MANAGER','7777',to_date('02.04.1981','DD.MM.RRRR '),'4','20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7698','BLAKE','MANAGER','7777',to_date('01.05.1981','DD.MM.RRRR '),null,'30'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7782','CLARK','MANAGER','7777',to_date('09.06.1981','DD.MM.RRRR '),null,'10'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7708','SCOTT','ANALYST','7566',to_date('09.12.1982','DD.MM.RRRR '),'7708','20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7777','KING','PRESIDENT',null,to_date('17.11.1981','DD.MM.RRRR '),null,'10'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7844','TURNER','SALESMAN','7698',to_date('10.09.1981','DD.MM.RRRR '),'0','30'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7876','ADAMS','CLERK','7639',to_date('12.01.1982','DD.MM.RRRR '),null,'20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7900','JAMES','CLERK','7698',to_date('03.12.1981','DD.MM.RRRR '),null,'31'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7902','FORD','ANALYST','7566',to_date('03.12.1981','DD.MM.RRRR '),null,'20'); Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO) values ('7934','MILLER','CLERK','7782',to_date('23.01.1982','DD.MM.RRRR '),null,'10'); CREATE UNIQUE INDEX "M_K" ON "EMP" ("EMPNO"); ALTER TABLE "EMP" MODIFY ("EMPNO" NOT NULL ENABLE); ALTER TABLE "EMP" ADD CONSTRAINT "M_K" PRIMARY KEY ("EMPNO") ENABLE; ALTER TABLE "EMP" ADD CONSTRAINT "EMP_FK_DEPT" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE; CREATE TABLE "EMP_ATTR_VALS" ( "DATE_BEG" DATE, "DATE_END" DATE, "VAL" VARCHAR2(64), "EMP_ATTR" VARCHAR2(32), "EMPNO" NUMBER ); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("DATE_BEG" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("DATE_END" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("VAL" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("EMP_ATTR" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" MODIFY ("EMPNO" NOT NULL ENABLE); ALTER TABLE "EMP_ATTR_VALS" ADD CONSTRAINT "EMP_AV_FK" FOREIGN KEY ("EMPNO") REFERENCES "EMP" ("EMPNO") ENABLE; Data
Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7369'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7499'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7876'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7900'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('09.01.2016','DD.MM.RRRR'),'100','SALARY','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('10.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'100','SALARY','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'120','SALARY','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'A','EDUCATION','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'B','EDUCATION','7934'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('29.02.2016','DD.MM.RRRR'),'150','SALARY','7521'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.03.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'180','SALARY','7521'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'B','EDUCATION','7521'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'C','EDUCATION','7521'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2017','DD.MM.RRRR'),'150','SALARY','7844'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2017','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'200','SALARY','7844'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.02.2016','DD.MM.RRRR'),'B','EDUCATION','7844'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.02.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7844'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'200','SALARY','7708'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'300','SALARY','7708'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.07.2016','DD.MM.RRRR'),'B','EDUCATION','7708'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.07.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7708'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'200','SALARY','7902'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'300','SALARY','7902'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7902'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7566'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'400','SALARY','7566'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7566'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7698'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'400','SALARY','7698'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'D','EDUCATION','7698'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('20.05.2016','DD.MM.RRRR'),'350','SALARY','7782'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('21.05.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'450','SALARY','7782'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'F','EDUCATION','7782'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'10000','SALARY','7777'); Insert into ZX.EMP_ATTR_VALS (DATE_BEG,DATE_END,VAL,EMP_ATTR,EMPNO) values (to_date('01.01.2016','DD.MM.RRRR'),to_date('01.01.2200','DD.MM.RRRR'),'A','EDUCATION','7777'); Log table
CREATE TABLE "EMP_CHK_LOG" ( "TS" TIMESTAMP (6), "STATUS" NUMBER, "XML" CLOB, "ERR_TXT" VARCHAR2(512) ); To work with the transaction scheme c example (ZX) will need some access to the scheme SYS.
CREATE OR REPLACE VIEW sys.v_transaction AS SELECT xidusn, xidslot, xidsqn,ses_addr, start_scn FROM v$transaction; GRANT SELECT ON sys.v_transaction TO zx; GRANT EXECUTE on sys. dbms_transaction TO zx; GRANT CREATE MATERIALIZED VIEW TO zx; Base table matvyu.
CREATE TABLE "EMP_CHK" ( "EMP_NO" NUMBER, "LTRANS_ID" VARCHAR2(64)); CREATE UNIQUE INDEX "EMP_CHK_PK" ON "EMP_CHK" ("LTRANS_ID"); ALTER TABLE "EMP_CHK" ADD CONSTRAINT "EMP_CHK_PK" PRIMARY KEY ("LTRANS_ID") ENABLE; ALTER TABLE "EMP_CHK" MODIFY ("LTRANS_ID" NOT NULL ENABLE); Create a materialized view.
CREATE MATERIALIZED VIEW chk_emp_mv REFRESH COMPLETE ON COMMIT AS SELECT emp_no, ltrans_id FROM emp_chk; Multi-user mode is provided as follows. At the beginning of each transaction, from the base table with the var_chk.cls_emp_chk procedure, the completed transaction information is deleted line by line. Using an autonomous transaction ensures clean-up regardless of the outcome (commit / rollback) of the current transaction. In the previous version, the whole table was cleared and, consequently, the entire table was locked until the end of the current transaction.
create or replace PACKAGE var_chk AS TYPE tab_str IS TABLE OF VARCHAR2(64); first_dml_in_commit NUMBER; var_emp_val CLOB; FUNCTION chk_on RETURN NUMBER; PROCEDURE write_log (p_status NUMBER ,p_err_txt VARCHAR2); PROCEDURE cls_emp_chk; END var_chk; 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; --------------------- PROCEDURE cls_emp_chk IS v_trans_arr tab_str := tab_str(); v_i NUMBER; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN FOR cur1 IN ( SELECT ltrans_id FROM emp_chk) loop v_trans_arr.extend; v_trans_arr(v_trans_arr.last) := cur1.ltrans_id; END LOOP; FOR i IN 1 .. v_trans_arr.count LOOP SELECT count(*) INTO v_i FROM sys.v_transaction t WHERE to_char(t.xidusn)||'.'||to_char(t.xidslot)||'.'||to_char(t.xidsqn) = v_trans_arr(i); IF v_i = 0 THEN DELETE FROM emp_chk WHERE ltrans_id = v_trans_arr(i); END IF; COMMIT; END LOOP; END; --------------------- BEGIN first_dml_in_commit :=1; dbms_lob.createtemporary(var_emp_val,true); END var_chk; create or replace TRIGGER dept_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 VAR_CHK.cls_EMP_CHK; END IF; var_chk.first_dml_in_commit := 0 ; END dept_bs; create or replace TRIGGER emp_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 var_chk.cls_emp_chk; END IF; var_chk.first_dml_in_commit := 0; END emp_bs; create or replace TRIGGER emp_attr_vals_bs BEFORE DELETE OR INSERT OR UPDATE ON emp_attr_vals BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; IF var_chk.first_dml_in_commit = 1 THEN var_chk.cls_emp_chk; END IF; var_chk.first_dml_in_commit := 0 ; END emp_attr_vals_bs; For each DML, the identifier of the controlled entity (in our example, an employee) and the identifier of the current transaction are written to the base table with any of the business information tables.
create or replace TRIGGER emp_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, sys.dbms_transaction.local_transaction_id AS ltrans_id FROM dual) b ON (a.emp_no = b.emp_no) WHEN NOT MATCHED THEN INSERT (a.emp_no, a.ltrans_id) VALUES (b.emp_no, b.ltrans_id) WHEN MATCHED THEN UPDATE SET a.ltrans_id = b.ltrans_id; END emp_ar; create or replace TRIGGER dept_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, dbms_transaction.local_transaction_id AS ltrans_id 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.ltrans_id) VALUES (b.emp_no, b.ltrans_id) WHEN MATCHED THEN UPDATE SET a.ltrans_id = b.ltrans_id; END dept_ar; create or replace TRIGGER emp_attr_vals_ar AFTER DELETE OR INSERT OR UPDATE ON emp_attr_vals 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, dbms_transaction.local_transaction_id AS ltrans_id FROM emp WHERE emp.empno = NVL(:new.empno, :old.empno) ) b ON (a.emp_no = b.emp_no) WHEN NOT MATCHED THEN INSERT (a.emp_no, a.ltrans_id) VALUES (b.emp_no, b.ltrans_id) WHEN MATCHED THEN UPDATE SET a.ltrans_id = b.ltrans_id; END emp_attr_vals_ar; Business control function.
create or replace FUNCTION emp_logic (p_emp_no NUMBER ,p_errtxt OUT VARCHAR2 ) RETURN NUMBER IS v_emp_count NUMBER; v_emp_loc VARCHAR2(16); v_job VARCHAR2(32); v_sal NUMBER; BEGIN ----------------------------------- SELECT dept.loc,COUNT(*) INTO v_emp_loc,v_emp_count FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.LOC in ( SELECT dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.empno = p_emp_no ) AND emp.job = 'CLERK' GROUP BY dept.loc; IF v_emp_count > 2 THEN p_errtxt:='. '||v_emp_loc||' 2 '; RETURN 1; END IF; ------------------------------------- FOR cur_d IN ( SELECT SUM(delt) sdelt, emp_attr FROM( SELECT t.date_beg, t.date_end,t.emp_attr, NVL(lead(t.date_beg) over(partition by t.empno, t.emp_attr order by t.date_beg, t.date_end)-1, t.date_end) - t.date_end delt FROM emp_attr_vals t WHERE t.empno = p_emp_no ) GROUP BY emp_attr ) LOOP IF cur_d.sdelt != 0 THEN p_errtxt:='. '||cur_d.emp_attr; RETURN 1; END IF; END LOOP; --------------------------------- SELECT emp.job, nvl((SELECT to_number(val) FROM emp_attr_vals eav WHERE eav.emp_attr = 'SALARY' AND SYSDATE BETWEEN eav.date_beg AND eav.date_end AND emp.empno = eav.empno ),0) sal INTO v_job ,v_sal FROM emp WHERE emp.empno = p_emp_no; IF v_job != 'PRESIDENT' AND v_sal > 999 THEN p_errtxt:='. 999'; RETURN 1; END IF; RETURN 0; END emp_logic; Trigger on matvyu.
Starting to clear the data of the previous transaction from the base table by matvue.
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; Starting the function of business control and writing to the log.
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); v_ltrans_id VARCHAR2(64); BEGIN IF var_chk.chk_on != 1 THEN RETURN; END IF; v_ltrans_id:=dbms_transaction.local_transaction_id; FOR cur IN (SELECT t.emp_no FROM chk_emp_mv t where t.ltrans_id=v_ltrans_id) LOOP SELECT XMLELEMENT("EMP", XMLAGG(XMLELEMENT("EMPNO",tb.empno, XMLELEMENT("ENAME", tb.ename), XMLELEMENT("JOB", tb.job), XMLELEMENT("MGR", tb.mgr), 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 ) ,(SELECT XMLELEMENT("ATTR_VAL", XMLAGG(XMLELEMENT("EMP_ATTR",k.emp_attr, XMLELEMENT("VAL",k.val), XMLELEMENT("DATE_BEG",k.date_beg), XMLELEMENT("DATE_END",k.date_end) ))) FROM emp_attr_vals k WHERE k.empno = tb.empno --7369 ) )) ).GETCLOBVAL() INTO var_chk.var_emp_val FROM ( SELECT * FROM EMP WHERE emp.empno = cur.emp_no ) tb GROUP BY empno,mgr,job,hiredate,ename,deptno,comm; -- v_result := 0; 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; Check the bike on the go.
We will check in two sessions. To control the process, the plus prompt displays the current time, and in the sid brackets of the current session.
Test 1. Waiting for locking.
Session 194695 opens a transaction.
20:43:48 zx(194695)@orcl> update emp set emp.comm = 100 where empno = 7566; 1 row updated. 20:50:02 zx(194695)@orcl> With an open transaction session 194695 session 194697 opens and fixes a new transaction.
20:50:22 zx(194697)@orcl> update emp set emp.comm = 100 where empno = 7708; 1 row updated. 20:50:53 zx(194697)@orcl> commit; Commit complete. 20:50:59 zx(194697)@orcl> Session 194695 successfully commits its transaction.
20:50:02 zx(194695)@orcl> commit; Commit complete. 20:51:12 zx(194695)@orcl> Test 2. Waits on locks when attempting to commit incorrect information in one of the sessions.
Session 194695 opens a transaction.
21:25:43 zx(194695)@orcl> update emp set emp.comm = 200 where empno = 7566; 1 row updated. 21:25:48 zx(194695)@orcl> With an open transaction of a session of 194695, a session of 194697 opens a transaction that, when attempted to commit, is rejected by the business relationship.
21:25:58 zx(194697)@orcl> update EMP_ATTR_VALS set date_end = TO_DATE('12012016','ddmmyyyy') where EMPNO=7369 and EMP_ATTR ='SALARY' and DATE_BEG = TO_DATE('01012016','ddmmyyyy'); 1 row updated. 21:26:32 zx(194697)@orcl> 21:26:33 zx(194697)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: . SALARY ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 21:26:43 zx(194697)@orcl> TO_DATE ( ' 21:25:58 zx(194697)@orcl> update EMP_ATTR_VALS set date_end = TO_DATE('12012016','ddmmyyyy') where EMPNO=7369 and EMP_ATTR ='SALARY' and DATE_BEG = TO_DATE('01012016','ddmmyyyy'); 1 row updated. 21:26:32 zx(194697)@orcl> 21:26:33 zx(194697)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: . SALARY ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 21:26:43 zx(194697)@orcl> = 21:25:58 zx(194697)@orcl> update EMP_ATTR_VALS set date_end = TO_DATE('12012016','ddmmyyyy') where EMPNO=7369 and EMP_ATTR ='SALARY' and DATE_BEG = TO_DATE('01012016','ddmmyyyy'); 1 row updated. 21:26:32 zx(194697)@orcl> 21:26:33 zx(194697)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: . SALARY ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 21:26:43 zx(194697)@orcl> = TO_DATE ( ' 21:25:58 zx(194697)@orcl> update EMP_ATTR_VALS set date_end = TO_DATE('12012016','ddmmyyyy') where EMPNO=7369 and EMP_ATTR ='SALARY' and DATE_BEG = TO_DATE('01012016','ddmmyyyy'); 1 row updated. 21:26:32 zx(194697)@orcl> 21:26:33 zx(194697)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: . SALARY ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 21:26:43 zx(194697)@orcl> Session 194695 successfully commits its transaction.
21:25:50 zx(194695)@orcl> commit; Commit complete. 21:26:53 zx(194695)@orcl> Test 3. Controlling the business rules of limiting the number of clerks in the branch.
21:46:50 zx(194695)@orcl> update emp set job = 'MANAGER' where empno = 7708; 1 row updated. 21:47:06 zx(194695)@orcl> commit; Commit complete. 21:47:11 zx(194695)@orcl> update emp set job = 'CLERK' where empno = 7708; 1 row updated. 21:47:32 zx(194695)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: . DALLAS 2 ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 21:47:37 zx(194695)@orcl> Test 4. Fulfillment of the business rule of wage control.
22:29:29 zx(194695)@orcl> update emp_attr_vals eav set eav.val='30000' where EAV.EMP_ATTR='SALARY' and sysdate BETWEEN eav.date_beg and EAV.DATE_END and eav.empno = 7777; 1 row updated. 22:29:36 zx(194695)@orcl> commit; Commit complete. 22:29:42 zx(194695)@orcl> update emp_attr_vals eav set eav.val='2000' where EAV.EMP_ATTR='SALARY' and sysdate BETWEEN eav.date_beg and EAV.DATE_END and eav.empno = 7708; 1 row updated. 22:30:27 zx(194695)@orcl> commit; commit * ERROR at line 1: ORA-12008: error in materialized view refresh path ORA-20555: . 999 ORA-06512: at "ZX.CHK_EMP_MV_AS", line 53 ORA-04088: error during execution of trigger 'ZX.CHK_EMP_MV_AS' 22:30:32 zx(194695)@orcl> Q.E.D. Naturally, we are talking about the amount of salary that has nothing to do with the contrast).
Source: https://habr.com/ru/post/282335/
All Articles