help for trigger problems

From: Ruiping Gao <ruiping_at_dpiwe.tas.gov.au>
Date: Tue, 29 Jun 1999 17:49:34 +1100
Message-ID: <37786C7D.8251A2BC@dpiwe.tas.gov.au>

Hello,

I have a table which has five fields (id, name,serial,mapno,siteno). People can put id,name, serial values into table. After people insert one record I want use trigger to populate mapno and siteno fields, which mapno=sub(serial,1,4) and siteno=sub(serial,5,8). I created a table and created a trigger and trigger code like following;

CREATE OR REPLACE TRIGGER mytrigger_lc

        BEFORE INSERT
        ON mytable
        For EACH ROW
        BEGIN
        :new.mapno := SUBSTR(:new.serial,1,4);
       :new.siteno := SUBSTR(:new.serial,5,8);
        insert into mytable (mapno,siteno) values

(':new.mapno',':new.siteno');
END;
/

When I start to insert id,name,serial values (for example: insert into mytable(id,name,serial) values (1,'Eucalyptus','1234AMBD'), then the following errors generated by ORACLE(Vertion 7.3.3):

ERROR at line 1:

ORA-01000: maximum open cursors exceeded
ORA-06512: at "mytrigger_lc", line 4
ORA-04088: error during execution of trigger 'mytrigger_lc'
ORA-06512: at "mytrigger_lc", line 5
ORA-04088: error during execution of trigger 'mytrigger_lc'

I just don't know what has happened. because trigger has been created without error. Could anybody there help me?

Thanks in advance.

Ruiping Gao

e_mail:ruiping_at_dpiwe.tas.gov.au Received on Tue Jun 29 1999 - 01:49:34 CDT