Hello Forum,
I am looking to Create a Lookup Table (To Create a Select Drop Down List) and was wondering how to enter the Data using a Sequence or Sequence with Combination of Trigger
I got it to work with Creating a Function and Combining a Created Sequence but was trying to accomplish with Sequence and Trigger
**************************************************************************************************************************************************
I get the following order trying to use seq.nextval ( I did get it to work Below utilizing Function and Sequence)
************************************************************************************************************************************************
I get the following order trying to use seq.nextval
CREATE TABLE StateLookup_2
(
StateID Number,
StateName NVARCHAR2 (32),
StateAbbrev CHAR (2)
);
ALTER TABLE StateLookup_2 ADD (
CONSTRAINT StateID_2_pk PRIMARY KEY (StateID));
Create Sequence StateID__2_Seq
Start With 1
Increment by 1
NoCache
NoCycle
INSERT ALL
INTO StateLookup_2 (StateID, StateName, StateAbbrev) VALUES (StateID__2_Seq.nextval,'Alabama', 'AL')
INTO StateLookup_2 (StateID, StateName, StateAbbrev) VALUES (StateID__2_Seq.nextval,'Alaska', 'AK')
INTO StateLookup_2 (StateID, StateName, StateAbbrev) VALUES (StateID__2_Seq.nextval,'Arizona', 'AZ')
INTO StateLookup_2 (StateID, StateName, StateAbbrev) VALUES (StateID__2_Seq.nextval,'Arkansas', 'AR')
INTO StateLookup_2 (StateID, StateName, StateAbbrev) VALUES (StateID__2_Seq.nextval,'California', 'CA')
INTO StateLookup_2 (StateID, StateName, StateAbbrev) VALUES (StateID__2_Seq.nextval,'Colorado', 'CO')
INTO StateLookup_2 (StateID, StateName, StateAbbrev) VALUES (StateID__2_Seq.nextval,'Connecticut', 'CT')
Select * from dual;
**************************************************************************************************************************************
I did get it to work utilizing Function and Sequence
**************************************************************************************************************************************
This Works
**************************************************************************************************************************************************
CREATE TABLE StateLookup
(
StateID Number,
StateName NVARCHAR2 (32),
StateAbbrev CHAR (2)
);
ALTER TABLE StateLookup ADD (
CONSTRAINT StateID_pk PRIMARY KEY (StateID));
Create Function Get_StateID_Seq
Return Number IS
Begin
Return StateID_seq.NextVal;
End;
/
Create Sequence StateID_Seq
Start With 1
Increment by 1
NoCache
NoCycle
INSERT ALL
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'Alabama', 'AL')
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'Alaska', 'AK')
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'Arizona', 'AZ')
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'Arkansas', 'AR')
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'California', 'CA')
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'Colorado', 'CO')
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'Connecticut', 'CT')
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'Delaware', 'DE')
INTO StateLookup (StateID, StateName, StateAbbrev) VALUES (Get_StateID_Seq,'District of Columbia', 'DC'
etc........)
Select * from Dual;