Re: How Can I do This

Manoj.V.S. wrote:
>
> Hello
> I had 3 table Period,Accode and Perbal, If I insert row in
> Accode, then Insert trigger of Accode, insert rows into perbal depending
> on period table. Perbal table is depending on Accode table ie. Foreign
> key references. When I had inserted row into accode table I got this
> error
> ERROR: ORA-04091:table GL.ACCODE is mutating, trigger/function may not
> see it
>
> Will somebody tell me how can I do this, what's wrong with this
> code. Any help in this regard is appreciated.
>
> Thank Q
> Waheed
> email:mbws_at_batelco.com.bh
>
> CREATE TABLE Period (
> Yno NUMBER(2),
> Pno NUMBER(2),
> CStart DATE,
> CEnd DATE,
> CONSTRAINT Pk_Period_YnoPno PRIMARY KEY(Yno,Pno));
>
> INSERT INTO Period Values (1,1,sysdate,sysdate);
> INSERT INTO Period Values (1,2,sysdate,sysdate);
> INSERT INTO Period Values (1,2,sysdate,sysdate);
>
> CREATE TABLE AcCode (
> Accode CHAR(5),
> Dep CHAR(2),
> OpenBal NUMBER(12,3),
> CONSTRAINT Pk_AcCode_AccDep PRIMARY KEY(AcCode,Dep)
> );
>
> /* PERBAL TABLE */
> CREATE TABLE PerBal (
> Accode CHAR(5),
> Dep CHAR(2),
> Yno NUMBER(2),
> Pno NUMBER(2),
> CONSTRAINT Fk_AccDep FOREIGN KEY(Accode,Dep) REFERENCES
> AcCode(AcCode,Dep) ON DELETE CASCADE,
> CONSTRAINT Pk_ADYN PRIMARY KEY(AcCode,Dep,Yno,Pno));
>
> /* TRIGGER ON AcCode Table */
>
> CREATE OR REPLACE TRIGGER Trig_AcCode_AI
> AFTER INSERT ON AcCode
> FOR EACH ROW
> DECLARE
> CURSOR CurPeriod IS SELECT yno,pno FROM Period;
> BEGIN
> FOR x IN CurPeriod LOOP
> INSERT INTO PERBAL (accode,dep,yno,pno)
> VALUES (:NEW.accode,:NEW.dep,x.Yno,x.Pno);
> END LOOP;
> END;
Triggers cannot access the table they are based upon.

i.e. if you have a trigger on perbal, you cannot select from perbal!

There are several very hack ways around it but the best way it to design it out.

Todd Verstraten
mailto:tverstrat_at_tier3.com
http://www.tier3.com Received on Wed Oct 30 1996 - 00:00:00 CET