A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
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
What is the better choice for primary key ? natural key or system-generated key ?
Auto Increment ID Field Table in the Oracle Database
Problem in using triggers and sequences
problem with the sequence generated by system
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2015