Re: constraint error message

A copy of this was sent to "Michele Baldessari" <michele.baldessari_at_NOSPAM.students.uni-linz.ac.at> (if that email address didn't require changing) On Tue, 16 Nov 1999 00:49:01 +0100, you wrote:

>hi!
>
>i have this pl/sql constraint testing program :
>
>CREATE OR REPLACE TRIGGER emp_del_trig
>BEFORE DELETE ON emp FOR EACH ROW
>DECLARE
> dummy INTEGER;
>BEGIN
> DBMS_OUTPUT.PUT_LINE('old.deptno :' || :old.deptno);
> IF DELETING THEN
> SELECT COUNT(*) INTO dummy FROM emp WHERE deptno = :old.deptno;
> DBMS_OUTPUT.PUT_LINE('value :' || dummy || ' old.deptno :' ||
>:old.deptno);
> IF dummy < 3 THEN raise_application_error( -20001, 'May not lower number
>of employees in this dept');
> END IF;
> END IF;
>END;
>
>but when I do the following statement I get error messages :
>DELETE FROM emp WHERE deptno=10;
>

This type of work must be done in an AFTER trigger (not for each row). Besides, you need to do this for an update statement as well. Doing that row by row will never work. Lets say you have 3 employees in dept 5 and 3 in dept 6. An update like:

update emp set deptno = decode( deptno, 5, 6, 6, 5 );

(that is -- make emps in dept 5 be in dept 6 and vice versa) would fail at the row level, but would succeed at the statement level. That is because the first update from dept 5 to 6 would lower dept 5's cound to 2 and that would 'violate' your rule. If the statement was allowed to execute to completion -- the statement would succeed.

even worse, if dept 6 has 4 employees and dept 5 has 3 -- depending on HOW the data was accessed, the update might work , might not (it would be wholly unpredicable). If the update processed:

change emp1 from 6 to 5 (ok, dept 6 has 3, dept 5 has 4) change emp2 from 5 to 6 (ok, dept 6 has 4, dept 5 has 3) change emp3 from 6 to 5
change emp4 from 5 to 6
change emp5 from 6 to 5
change emp6 from 5 to 6
change emp7 from 6 to 5

That would work but if the data was accessed different (same data) the outcome would be different (herein lies the cause of the mutating table error).

See the website in my url for a HOWTO on avoiding mutating tables. basically, you'll process your rule AFTER the delete/update has finished -- in an AFTER trigger.

>
>old.deptno :10
>DELETE FROM emp WHERE deptno=10
>*
>ERROR at line 1:
>ORA-04091: table IFS49.EMP is mutating, trigger/function may not see it
>ORA-06512: at "IFS49.EMP_DEL_TRIG", line 6
>ORA-04088: error during execution of trigger 'IFS49.EMP_DEL_TRIG'
>
>
>old.deptno :10
>
>What I am doing wrong? Thank you very much for any help. I am an oracle
>newbie and I am quite clueless on what's going on.
>
>Ciao,
>Michele
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 16 1999 - 07:20:25 CST