The reason is because of the recursion.
When you insert a record into the table, your trigger has been triggered and
executed. The trigger will insert another record and trigger an new before
insert trigger. Thus it will never end unless it out the limitation of some
recourses or even end the session.
Actully you just delete the insert statment in your trigger, then everything
should be OK.
Ruiping Gao wrote in message <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 - 04:32:41 CDT
