| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: help for trigger problems
On Tue, 29 Jun 1999 17:49:34 +1100, Ruiping Gao <ruiping_at_dpiwe.tas.gov.au>
wrote:
>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'
>
You are inserting into the same table that the trigger is on which will cause the trigger to file again, which will cause the trigger to file again, etc... If you want the trigger to populate the two fields ( mapno and siteno ) of the same record being inserted then all you need to do is assign the values to those fields.
eg.
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,4);
END;
/
Using the insert statement:
insert into mytable( id, name, serial ) values ( 1, 'Eucalyptus', '1234AMBD' )
Will get the desired effect.
Note: I changed the substr of the :new.siteno to SUBSTR(:new.serial,5,4) which says give me the next 4 characters starting at the 5th character. You had substr(:new.serial,5,8) which says give me the next 8 characters starting at the 5th one. Since you only are inserting 8 characters into the serial column, I assume that you want just the second set of 4 characters.
hope this helps.
chris.
>
>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
>
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |