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.
Comments
Post a Comment