File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
Win a copy of
Murach's Java Servlets and JSP
this week in the
Problem creating surrogate key
Atanas The dude
Joined: Mar 30, 2011
Mar 30, 2011 04:41:43
I am trying to create a surrogate key so for that purpose I have this sequence:
CREATE SEQUENCE Author_Seq INCREMENT BY 1 START WITH 1 MINVALUE 1
but when I try to create the following trigger:
CREATE OR REPLACE TRIGGER author_sur BEFORE INSERT ON AUTHOR FOR EACH ROW WHEN (NEW.ID IS NULL) BEGIN :NEW.ID := Author_Seq.NEXTVAL; END;
IN- or OUT-parameter is missing at index:: 1
. Any idea why this happens?
Joined: Oct 11, 2000
Mar 30, 2011 23:53:04
You need to change the body of the trigger.
A simple Internet search for the terms "oracle", "trigger", "nextval" will provide the answer.
Try the following:
SELECT Author_Seq.NEXTVAL INTO :new.id FROM dual;
It is sorta covered in the
JavaRanch Style Guide
subject: Problem creating surrogate key
Auto Increment ID Field Table in the Oracle Database
problem with the sequence generated by system
Problem in using triggers and sequences
What is the better choice for primary key ? natural key or system-generated key ?
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2014