Only one current task for each employee

From: AK <ak_tiredofspam_at_yahoo.com>
Date: 29 Nov 2004 12:42:39 -0800
Message-ID: <46e627da.0411291242.72de6499@posting.google.com>

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 Received on Mon Nov 29 2004 - 14:42:39 CST