Change the trigger to look like this:
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);
END;
Ruiping Gao <ruiping_at_dpiwe.tas.gov.au> wrote in message
news:37786C7D.8251A2BC_at_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 - 10:27:24 CDT
