create or replace trigger.............
R. RoyalApr 19 2004 — edited Apr 21 2004I 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