Re: Only one current task for each employee

AK wrote:

> I need to implement a business rule 'Only one current task for each
> employee'.
> Almost all the tasks are in 'completed' state. There may be any amount
> of tasks per employee in any other states.
>
> I don't think a simple trigger can enforse this business rule. I tried
> this:
>
> DROP TRIGGER A_UNIQUE_TEST_INS;
> DROP TABLE A_UNIQUE_TEST;
> /
> CREATE TABLE A_UNIQUE_TEST(EMPLOYEE_PK NUMBER NOT NULL,
> TASK_NUMBER NUMBER NOT NULL, STATUS NUMBER);
> ---------- status 1 means 'current'
> ALTER TABLE A_UNIQUE_TEST ADD CONSTRAINT A_UNIQUE_TEST_PK PRIMARY
> KEY(EMPLOYEE_PK, TASK_NUMBER);
> /
> CREATE OR REPLACE TRIGGER A_UNIQUE_TEST_INS
> BEFORE INSERT ON A_UNIQUE_TEST
> FOR EACH ROW
> DECLARE C NUMBER;
> BEGIN
> SELECT COUNT(*) INTO C FROM A_UNIQUE_TEST
> WHERE A_UNIQUE_TEST.EMPLOYEE_PK = :NEW.EMPLOYEE_PK
> AND A_UNIQUE_TEST.TASK_NUMBER <> :NEW.TASK_NUMBER
> AND :NEW.STATUS = 1
> AND A_UNIQUE_TEST.STATUS = 1;
> IF (C>0)
> THEN
> RAISE NO_DATA_FOUND;
> END IF;
> END;
> /
> INSERT INTO A_UNIQUE_TEST VALUES(1,1,5);
> INSERT INTO A_UNIQUE_TEST VALUES(1,2,5);
> INSERT INTO A_UNIQUE_TEST VALUES(1,3,5);
> INSERT INTO A_UNIQUE_TEST VALUES(1,4,1);
> -------- this last statement failed all right, as it should
> INSERT INTO A_UNIQUE_TEST VALUES(1,5,1);
> SELECT * FROM A_UNIQUE_TEST;
> /
>
> seems to work from one connection, but I can run
>
> INSERT INTO A_UNIQUE_TEST VALUES(1,5,1);
>
> from another connection, then commit both transactions, and here we
> go:
>
>
> SELECT * FROM A_UNIQUE_TEST
> EMPLOYEE_PK TASK_NUMBER STATUS
> ----------- ----------- ----------
> 1 1 5
> 1 2 5
> 1 3 5
> 1 4 1
> 1 5 1
> 5 rows selected
>
> tasks number 4 and 5 are current for employee 1. Not good.
> From the very beginning I was very sceptical about using a trigger to
> enforse the rule, and it was very easy to build a case when a trigger
> has failed to enforse the business rule.
> So I dropped the useless trigger and created a function based index
> instead:
>
> CREATE UNIQUE INDEX A_UNIQUE_TEST_U1
> ON A_UNIQUE_TEST(DECODE(STATUS, 1, EMPLOYEE_PK, NULL), DECODE(STATUS,
> 1, STATUS, NULL));
>
> the unique index won't let me enter a violating row from another
> connection:
> INSERT INTO A_UNIQUE_TEST VALUES(1,5,1);
> fails as soon as I commit the first transaction.
>
> So I think a unique function based index is a solution, while a
> trigger is not. But I was a little bit biased against using a trigger
> from the very beginning, so I'd appreciate any comments

I think this is school work and you need to work this out for yourself.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)