Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: constraint error message

Re: constraint error message

From: Lee Kyoung Rok <tunnel_at_hananet.net>
Date: Tue, 16 Nov 1999 10:42:24 +0900
Message-ID: <vF2Y3.2520$435.75035@news.hananet.net>

Michele Baldessari <michele.baldessari_at_NOSPAM.students.uni-linz.ac.at>ÀÌ(°¡) ¾Æ·¡ ¸Þ½ÃÁö¸¦ news:38309c26_at_alijku02.edvz.uni-linz.ac.at¿¡ °Ô½ÃÇÏ¿´½À´Ï´Ù.
> 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;
>
>
> 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
>
>

dummy must have : in its head like below:

:dummy Received on Mon Nov 15 1999 - 19:42:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US