I'm trying to create a basic "states" table of U.S. listing with abbreviations, and an ID for each. I'm trying to accomplish this by selecting a distinct query of state abbreviations from another table, and then inserting that into a new table and have an ID automatically get assigned via a trigger.
My trigger looks like this:
CREATE OR REPLACE TRIGGER "SCHEMA"."STATE_T1"
insert on STATESTBL for each row
IF :NEW.ST_ID IS NULL THEN
SELECT STNAMESEQ.NEXTVAL INTO :NEW.ST_ID FROM DUAL;
I'm trying the following insert statement
INSERT INTO STATESTBL
(STNAMESEQ.NEXTVAL, (SELECT DISTINCT STATE FROM NETWORKORDERS WHERE STATE IS NOT NULL ))
I must be missing something obviously, but I'm not figuring it out at the moment. Can anyone give me any direction on this, please?
My hope on this was that I'd get a collection of about 54 rows (there are some things like VI, PR, etc other than the main 50) and those rows would get created.