How Can I do This

From: Manoj.V.S. <mbws_at_batelco.com.bh>
Date: 1996/10/27
Message-ID: <3272BEC0.489F_at_batelco.com.bh>#1/1

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; Received on Sun Oct 27 1996 - 00:00:00 CEST