SQL Table is mutating... Error

Question

I'm attempting to write a trigger that will disallow any room in a hospital to have more than 3 services. The table RoomServices has a room number and a service that it has. So the only way to determine this is to group the rooms by room number and count the services. I have tried the code:

CREATE TRIGGER RoomServiceLimit
BEFORE INSERT OR UPDATE ON RoomServices
FOR EACH ROW
DECLARE
    numService NUMBER;
    CURSOR C1 IS SELECT count(*) AS RoomCount FROM RoomServices WHERE roomNumber = :new.roomNumber;
BEGIN

IF(inserting) THEN
    SELECT count(*) into numService FROM RoomServices WHERE roomNumber = :new.roomNumber;
    if(numService > 2) THEN
        RAISE_APPLICATION_ERROR(-20001,'Room ' || :new.roomNumber || ' will have more than 3 services.');
    END IF;
END IF;

IF(updating) THEN
    FOR rec IN C1 LOOP
        IF(rec.RoomCount > 2) THEN
            RAISE_APPLICATION_ERROR(-20001,'Room ' || :new.roomNumber || ' will have more than 3 services.');
        END IF;
    END LOOP;
END IF;
END;    
/

I've tried running each method separately with insert and update, and inserting always works and updating will always give me the mutating table error. I don't know how else to go about solving this problem, so any advice would be greatly appreciated.

Thanks!

Answer 1

There is no reliable way to enforce this kind of constraint using triggers. One possible approach is to use a materialized view that automatically refreshes on commit and has a check constraint enforcing your business rule:

create table roomservices (
  pk number not null primary key,
  roomnumber number);


create materialized view mv_roomservices  
refresh on commit as
select 
  pk,
  roomnumber,
  count(*) over (partition by roomnumber) as cnt 
from roomservices;

alter table mv_roomservices add constraint 
  chk_max_2_services_per_room check (cnt <= 2);  

Now, whenever you add more than two services for a room and try to commit your transaction, you will get a ORA-12008 exception (error in materialized view refresh path).