I have created a sequence in oracle apex and linked the sequence to the row on the application end. However, on the master-detail form, it doesn't auto increment to the new Roster_ID when I try to add a new form record. What could be my mistake? Please help
Here is my code:
CREATE TABLE ROSTER(
Roster_ID NUMBER(5),
Working_Hours NUMBER(2),
Shift_Date DATE,
Shift_End_Date DATE,
Shift_ID NUMBER(5) REFERENCES Shift (Shift_ID),
Ward_ID NUMBER(5) REFERENCES WARD (Ward_ID),
Ward_Name VARCHAR2(25) REFERENCES WARD (Ward_Name),
CONSTRAINT PK_ROSTER PRIMARY KEY (Roster_ID),
CONSTRAINT CHECK_WORKING_HOURS CHECK (Working_Hours IN (6, 8, 12))
);
CREATE SEQUENCE Roster_seq START WITH 5 INCREMENT BY 1 NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER Roster_T1
BEFORE
INSERT ON Roster
FOR EACH ROW
BEGIN
IF :NEW.Roster_ID is NULL THEN
select Roster_seq.nextval into :NEW.Roster_ID from SYS.DUAL;
END IF;
END;
/
COMMIT;
/
There's no need for a trigger; you could have set roster_id
's default value to sequence roster_seq
.
Apart from that, set roster_id
to be a
If you then run a page and add a new row, Apex will let you enter all data you need, and save new row(s) along with the next sequence values in roster_id
column.
If you want to see roster_id
's value, then create a new column, let's call it roster_id_display
and set it to be
ROSTER_ID
whose datatype is numberNow everything works: you can enter new rows and see IDs, but you can't (and shouldn't, anyway) change it via Interactive Grid.