Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

create or replace trigger.............

R. RoyalApr 19 2004 — edited Apr 21 2004
I have table AB:
AB_ID VARCHAR2(32) PRIMARY KEY
BF_ID VARCHAR2(8)
FS_ID VARCHAR2(8)

I'd like to create a trigger that insert automatically AB_ID when I insert a new BF_ID and FS_ID
(AB_ID=BF_ID.FS_ID+1)

FOR EXAMPLE:
AB_ID...........BF_ID............FS_ID
001.346..........001..............345
023.124..........023..............123
012.002..........012..............001

I tried this:
CREATE OR REPLACE TRIGGER AB_PREINSERT
BEFORE INSERT ON AB
REFERENCING
NEW AS NEW
OLD AS OLD
FOR EACH ROW
Begin
select to_number(:NEW.BF_ID)||'.'||TO_NUMBER(:NEW.FS_ID)+1
into :NEW.AB_ID
from AB;
End;


ORA-01722: INVALID NUMBER
ORA-06512: at "AB_PREINSERT", line 2

How can I create the trigger to get this code AB_ID?
Must I create a sequence??

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 19 2004
Added on Apr 19 2004
11 comments
1,004 views