| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Pls Help- what is wrong with my code?
Johnson Chao wrote:
>
> 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.
Basically you want:
before-statement
initialise a pl/table in a package
before-row
store the old and new values in this plsql table
after statment
loop thru each entry in your pl/sql table and perform
the actions that you wanted to do in the before row trigger
but could not (due to the mutating prob)
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Fri Aug 06 1999 - 04:22:24 CDT
 |
 |