ORA-01422: exact fetch returns more than requested number of rows Trigger error

Question

What I am trying to do BEFORE INSERT trigger involving the PATIENT table that was designed to prohibit the assignment of two patients to the same room where the difference in their ages is greater than 10 years. In addition, the trigger was also supposed to prohibit two patients with allowable ages from being assigned to the same bed.

CREATE TABLE CODE
set linesize 130;
set pagesize 100;
drop table patient cascade constraints;
create table patient (pat_id char(7), 
pat_name varchar(41) constraint nn_patnm not null,
pat_gender char(1),
pat_age number(2) constraint nn_patage not null,
pat_admit_dt date constraint nn_patadmdt not null,
pat_wing char(1),
pat_room# number,
pat_bed char(1),
constraint pk_pat primary key (pat_id),
constraint chk_gender check (pat_gender in ('M', 'F')),
constraint chk_age check (pat_age between 1 and 90),
constraint chk_bed check(pat_bed in ('A', 'B')));
insert into patient values ('DB77642', 'Davis, Bill', 'M', 38, '14-DEC-2017', 'A', 16, 'A');
insert into patient values ('GD72222', 'Grimes, David', 'M', 23, '14-DEC-2017', 'B', 17, 'A');
insert into patient values ('LH97384', 'Lisauckis, Hal', 'M', 28, '14-DEC-2017', 'B', 17, 'B');
insert into patient values ('RN31679', 'Robbins, Nancy', 'F', 23, '14-DEC-2017', 'A', 20, 'A');
insert into patient values ('HR32343', 'Howell, Sue', 'F', 20, '14-DEC-2017', 'A', 21, 'B');
desc patient
select * from patient;
commit;

LIST I AM USING TO TEST THE TRIGGER

column pat_name format a18
column pat_gender format a10
column pat_admit_dt format a13
column pat_wing format a8
column pat_bed format a8
set feedback on
insert into patient values ('AZ24523', 'Zhou, Alicia', 'F', 24, '14-DEC-2016', 'A', 20, 'B');
select * from patient;
insert into patient values ('JA33234', 'Abbott, John', 'M', 50, '14-DEC-2016', 'A', 16, 'B');
select * from patient;
insert into patient values ('AN32676', 'Newman, Andrew', 'M', 10, '14-DEC-2016', 'A', 16, 'B');
select * from patient;
insert into patient values ('ZZ24523', 'Zhang, Zhaoping', 'F', 38, '14-DEC-2016', 'A', 16, 'A');
select * from patient;
insert into patient values ('PS76234', 'Savant, Priyanka', 'F', 21, '14-DEC-2016', 'A', 21, 'B');
select * from patient;
insert into patient values ('SS43823', 'Sundar, Sarayu', 'F', 24, '14-DEC-2016', 'A', 21, 'A');
select * from patient;
insert into patient values ('PM43453', 'Perrin, Michael', 'M', 43, '14-DEC-2016', 'B', 22, 'A');
select * from patient;

TRIGGER

create or replace trigger patient_ins_row
before insert on patient
for each row

declare
room_age number;
difference number;
pat_name1 varchar2(41);
pat_bed1 char(1);
pat_room1 number;
ERROR_MESSAGE EXCEPTION;
BEGIN

select pat_name, pat_age, pat_room#, pat_bed into pat_name1, room_age, pat_room1, pat_bed1
from patient where pat_room# = :new.pat_room#;
if (:new.pat_bed = pat_bed1)
then DBMS_OUTPUT.PUT_LINE ('Cannot Assign ' || :new.pat_name || ' to bed already occupied ' || :new.pat_room# || 'years');
else
difference := :new.pat_age - room_age;
end if;

if (difference > 10)
then RAISE ERROR_MESSAGE;
elsif (difference < 10) then
DBMS_OUTPUT.PUT_LINE ('New Patient: ' || :new.pat_name || ' is too young: age difference is ' || difference || 'years');
else
dbms_output.put_line ('Successful insertion of ' || :new.pat_name || 'in room' || pat_room1);
END IF;

EXCEPTION
WHEN ERROR_MESSAGE THEN
RAISE_APPLICATION_ERROR (-20001, 'New Patient: ' || :new.pat_name || ' is too old: age difference is ' || difference || 'years');
END;

I keep getting the error in my title for many of the inserts. Don't know what I am doing wrong.

Answer 1

The problem ORA-01422 raises due to this statement :

select pat_name, pat_age, pat_room#, pat_bed 
  into pat_name1, room_age, pat_room1, pat_bed1
  from patient where pat_room# = :new.pat_room#;

Since, your consecutive statement try to select into pat_room1 from repeatedly used :new.pat_room# with values like 16 and 21.

As an example, in the testing set when you apply

insert into patient values ('JA33234', 'Abbott, John', 'M', 50, '14-DEC-2016', 'A', 16, 'B'); 

the above select would give more than one row for value 16 of :new.pat_room#, and too_many_rows error will raise.