sql - Oracle - How to force user to INSERT multiple row -

i using oracle 11gr2 on academic assignment. there constraint room must have 3 - 5 people. know can write trigger check if room has more 5 people:

create table people (   pid integer primary key );  create table room (   rid integer primary key );  create table living (   rid integer,   pid integer,   constraint living_pk primary key (rid, pid),   constraint living_fk_rid foreign key (rid) references room(rid),   constraint living_fk_pid foreign key (pid) references people(pid) );  create or replace trigger living_biu   before insert or update on living   referencing new new old old   each row declare   count number; begin   select count(*)     count     living     rid = :new.rid;   if(count > 5)       raise_application_error(-20002, 'too many people in room.');   end if; end living_bi; 

but can not check if number lesser 3 because can not insert things living. question how can create trigger force user insert more 3 rows , less 5 rows @ time?

with standard preface isn't how sort of thing in real world...

you realistically need use statement-level trigger here. if don't mind performance hit of checking every room every time

create or replace trigger living_aiud   after insert or update or delete   on living declare   count number; begin   x in (select rid, count(*) cnt               living              group rid             having count(*) < 3)   loop     raise_application_error(-20002, 'too few people in room ' || x.rid);   end loop; end living_aiud; 

if don't want check every room every time, you'd need package collection of rid values, before statement trigger initialized collection, , row-level trigger added :new.rid value collection. after statement trigger iterate on elements in collection , check number of people in rooms.