Skip to Main Content

subquery error returning more than one row with trigger

user515689Apr 4 2014 — edited Apr 4 2014

Hi folks,

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"
BEFORE
insert on STATESTBL for each row
begin
IF :NEW.ST_ID IS NULL THEN
       SELECT STNAMESEQ.NEXTVAL INTO :NEW.ST_ID FROM DUAL;
     END IF;

end;
/

I'm trying the following insert statement

INSERT INTO STATESTBL

(ST_ID,STATE_ABR)

VALUES

(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.


Thank you!

Comments
Post Details
Added on Apr 4 2014
3 comments
87 views