Description
I'm trying to design a work flow component for a small application that I'm developing. The work flow contains a number of steps (STEPS entity). Each step has one or more possible outcomes (STEP_OUTCOMES entity via OUTCOMES entity). A step is related to another step and outcome in a many-to-many relationship (STEP_TO_STEP entity). The TRANSACTIONS entity models the completion of the various steps.
In a perfect world I would love to be able to make a declarative constraint that says before you can complete a step the correct previous steps must be completed. Unfortunately due to the complexity of the logic I can't declare a constraint and, with what I have put together below a REFRESH ON COMMIT materialized view is not possible.
So what I have decided to do was code up an assertion and I plan to include that assertion in my PL/SQL logic to prior to completing a transaction. I have two questions:
1. Can my assertion below be simplified any further to be more readable/maintainable or efficient?
2. Should I consider applying this assertion (wrapped up in a package/procedure of course :) ) as a row level trigger upon insert into TRANSACTIONS, or keep it in the PL/SQL code? (I ask because I know some people despise triggers but this seems like a good use for it and a trigger is closer to the data then a PL/SQL package in my opinion).
You can find my assertion, version, test cases and expected results below.
I apologize for the verbosity of this post, but I did my best to provide a concise example that accurately demonstrated the problem. If you have any other questions please let me know.
Logic
In order for a step to be considered eligible for completion its parent steps must be completed. Considering the sample data below and test case 1 as an example. In order for Step 8 to be considered eligible for completion Step 5, Step 3, and either Step 6 or Step 7 must have a transaction record. So the correct possibilities would be (Step 5, Step 3, and Step 6) or (Step 5, Step 3, and Step 7). The optionality in Step 6/7 exists because they have the same parent (Step 4) and choosing either Step 6 or Step 7 depends on the outcome defined in the STEP_TO_STEP entity.
Version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Setup
DROP TABLE transactions;
DROP TABLE step_to_step;
DROP TABLE step_outcomes;
DROP TABLE outcomes;
DROP TABLE steps;
CREATE TABLE steps
(
step_id NUMBER PRIMARY KEY
, step_name VARCHAR2(10) NOT NULL
);
INSERT INTO steps(step_id, step_name) VALUES(1,'A');
INSERT INTO steps(step_id, step_name) VALUES(2,'B');
INSERT INTO steps(step_id, step_name) VALUES(3,'C');
INSERT INTO steps(step_id, step_name) VALUES(4,'D');
INSERT INTO steps(step_id, step_name) VALUES(5,'E');
INSERT INTO steps(step_id, step_name) VALUES(6,'F');
INSERT INTO steps(step_id, step_name) VALUES(7,'G');
INSERT INTO steps(step_id, step_name) VALUES(8,'H');
INSERT INTO steps(step_id, step_name) VALUES(9,'I');
CREATE TABLE outcomes
(
outcome VARCHAR2(10) PRIMARY KEY
);
INSERT INTO outcomes(outcome) VALUES('APPROVE');
INSERT INTO outcomes(outcome) VALUES('DISAPPROVE');
INSERT INTO outcomes(outcome) VALUES('COMPLETE');
CREATE TABLE step_outcomes
(
step_id NUMBER
, outcome VARCHAR2(10)
, CONSTRAINT so_step_id_fk FOREIGN KEY (step_id) REFERENCES steps(step_id)
, CONSTRAINT so_outcome_fk FOREIGN KEY (outcome) REFERENCES outcomes(outcome)
, CONSTRAINT so_step_outcomes_pk PRIMARY KEY (step_id,outcome)
);
INSERT INTO step_outcomes(step_id,outcome) VALUES (1,'COMPLETE');
INSERT INTO step_outcomes(step_id,outcome) VALUES (2,'COMPLETE');
INSERT INTO step_outcomes(step_id,outcome) VALUES (3,'COMPLETE');
INSERT INTO step_outcomes(step_id,outcome) VALUES (4,'APPROVE');
INSERT INTO step_outcomes(step_id,outcome) VALUES (4,'DISAPPROVE');
INSERT INTO step_outcomes(step_id,outcome) VALUES (5,'COMPLETE');
INSERT INTO step_outcomes(step_id,outcome) VALUES (6,'COMPLETE');
INSERT INTO step_outcomes(step_id,outcome) VALUES (7,'COMPLETE');
INSERT INTO step_outcomes(step_id,outcome) VALUES (8,'COMPLETE');
CREATE TABLE step_to_step
(
parent_step_id NUMBER
, parent_outcome VARCHAR2(10)
, child_step_id NUMBER
, CONSTRAINT sts_pstep_outcome_fk FOREIGN KEY (parent_step_id,parent_outcome) REFERENCES step_outcomes(step_id,outcome)
, CONSTRAINT sts_cstep_id_fk FOREIGN KEY (child_step_id) REFERENCES steps(step_id)
, CONSTRAINT step_to_step_pk PRIMARY KEY (parent_step_id,parent_outcome,child_step_id)
);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(1,'COMPLETE',2);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(1,'COMPLETE',3);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(1,'COMPLETE',4);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(2,'COMPLETE',5);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(3,'COMPLETE',8);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(4,'APPROVE',6);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(4,'DISAPPROVE',7);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(5,'COMPLETE',8);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(6,'COMPLETE',8);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(7,'COMPLETE',8);
INSERT INTO step_to_step(parent_step_id, parent_outcome, child_step_id) VALUES(8,'COMPLETE',9);
CREATE TABLE transactions
(
step_id NUMBER
, outcome VARCHAR2(10)
, trans_date DATE
, CONSTRAINT tran_step_outcome_fk FOREIGN KEY (step_id,outcome) REFERENCES step_outcomes(step_id,outcome)
);
Assertion
SELECT 1
FROM dual
WHERE EXISTS
( -- Compare the number of transactions to the number of parents. If they are not equal
-- then one of the children with no siblings did not complete a transaction or siblings
-- with the same parent did not complete a transaction
SELECT NULL
FROM
( -- A child must have a parent, and may have siblings. Group by parent
-- to determine if there are any transactions present by one of the children.
SELECT stm.parent_step_id
, COUNT(CASE WHEN tran.trans_date IS NOT NULL THEN 1 END) AS num_transactions
FROM
(
SELECT a.child_step_id AS step_id
, a.parent_step_id
, stou.outcome
, MAX(a.lvl) OVER () AS max_lvl
, a.lvl
FROM
( -- Build parent child hierarchcy from the bottom up. The terminology
-- in the comments above refer to parents and children as if the actual
-- relationship was top to bottom
SELECT stst.child_step_id
, stst.parent_step_id
, LEVEL AS lvl
FROM step_to_step stst
WHERE level <= 2
START WITH child_step_id = :start_step
CONNECT BY PRIOR stst.parent_step_id = stst.child_step_id
) a
JOIN step_outcomes stou ON stou.step_id = a.child_step_id
) stm
LEFT JOIN transactions tran ON stm.step_id = tran.step_id AND stm.outcome = tran.outcome
WHERE stm.max_lvl = stm.lvl -- filter rows based on the maximum level, this allows
-- a comparison regardless of whether the start step
-- has a grandparent or not. If this condition is not there
-- the counts report incorrect number of missing transactions.
GROUP BY stm.parent_step_id
)
HAVING SUM(num_transactions) = COUNT(*)
)
;
Test 1
var start_step NUMBER;
exec :start_step := 8;
DELETE FROM transactions;
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(1,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(2,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(3,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(4,'APPROVE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(5,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(7,'COMPLETE',SYSDATE);
expected result = 1
Test 2
var start_step NUMBER;
exec :start_step := 8;
DELETE FROM transactions;
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(1,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(2,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(3,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(4,'APPROVE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(7,'COMPLETE',SYSDATE);
expected result = "no rows selected"
Test 3
var start_step NUMBER;
exec :start_step := 8;
DELETE FROM transactions;
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(1,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(2,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(3,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(4,'APPROVE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(5,'COMPLETE',SYSDATE);
expected result = "no rows selected"
Test 4
var start_step NUMBER;
exec :start_step := 3;
DELETE FROM transactions;
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(1,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(3,'COMPLETE',SYSDATE);
expected result = 1
Test 5
var start_step NUMBER;
exec :start_step := 9
DELETE FROM transactions;
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(1,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(2,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(3,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(4,'APPROVE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(5,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(7,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(8,'COMPLETE',SYSDATE);
expected result = 1
Test 6
var start_step NUMBER;
exec :start_step := 9
DELETE FROM transactions;
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(1,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(2,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(3,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(4,'APPROVE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(5,'COMPLETE',SYSDATE);
INSERT INTO transactions(step_id, outcome, trans_date) VALUES(7,'COMPLETE',SYSDATE);
expected result = "no rows selected"
Test 7
var start_step NUMBER;
exec :start_step := 3;
DELETE FROM transactions;
expected result = "no rows selected"