Re: Pls Help- what is wrong with my code?

From: Johnson Chao <zjx110_at_my-deja.com>
Date: Fri, 06 Aug 1999 01:10:16 GMT
Message-ID: <7odcll$8e7$1@nnrp1.deja.com>

Hello,:
  Thanks for your help. Now I understand that in oralce, in the middle of a statement executing , means "mutating", I can not read or write to the same table in the trigger.
 But still, my problems exists. And my condition is a little more complex .
 I was told to remove "for each row" from my trigger, but I have to include it because I have to access the :Old record. My trigger is like below:

 create or replace trigger aaa
 After Delete on ztable
 for each row
 DECLARE
         i integer;
 BEGIN

        select count(*) into i from ztable where c2=:old.c2;
         if i=0 then
              delete * from ztablenew where c2new=:old.c2;
           end if;

 END; How can I do about it?

Thanks.

In article <37A98203.651F_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> Johnson Chao wrote:
> >
> > Hello:
> > I want to make a very easy trigger like this.
> >
> > ztable with c1, c2 two columns
> > After delete a record from ztable, check to see if there is still
> > records in ztable where c2=5, then my code is as followed:
> >
> > create or replace trigger aaa
> > After Delete on ztable
> > for each row
> > DECLARE
> > i integer;
> > BEGIN
> > select count(*) into i from ztable where c2=5;
> > if i=0 then
> > -- do something
> > end if;
> > END;
> >
> > Though there is no compile error on it, when I delete a record from
> > ztable1, I was told ora4091 error ."ztable1 is mutating, ...can not
see
> > it".
> > What is wrong with my code?
> >
> > Thanks
> >
> > --
> > Johnson Chao
> > ctc Japan
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.

>

> Your problem is described in the oracle application developers guide
> (chapt 9 for v7, chapt 13 for v8)...

>

> Search www.deja.com for "mutating tables" and you find numerous
> solutions
> --
> ===========================================
> Connor McDonald
> "These views mine, no-one elses etc etc"
> connor_mcdonald_at_yahoo.com

>

> "Some days you're the pigeon, and some days you're the statue."
>

--
Johnson Chao
ctc Japan

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 05 1999 - 20:10:16 CDT