Auto Increment
430789Oct 13 2004 — edited Oct 13 2004I know that Oracle doesn't support a field of data type auto_increment and that you can simulate this behavior by creating a sequence that is updated by a trigger every time a table is updated...but how do you handle a field that sometimes should be auto incremented and sometimes has a value directly inserted into it. I have the following:
CREATE TABLE tblComponents (
id INT NOT NULL,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (id),
);
CREATE SEQUENCE tblComponents_Seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE;
CREATE TRIGGER tblComponents_Trig
BEFORE INSERT ON tblComponents
FOR EACH ROW
BEGIN
SELECT tblComponents_Seq.nextval INTO :new.id from DUAL;
END;
/
This works great as long as I never directly insert an id into tblComponents but sometimes I need to and the sequence is not updated...so when the sequence finally gets to the id number I just inserted it collides. How do I insert the highest id in tblComponents +1 in a trigger?
Thanks,
Jim