Таблица SQL мутирует Ошибка

Не существует надежного способа применить такое ограничение с помощью триггеров. Один из возможных подходов — использовать материализованное представление, которое автоматически обновляется при фиксации и имеет проверочное ограничение, обеспечивающее выполнение вашего бизнес-правила:

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);  

Теперь всякий раз, когда вы добавляете более двух сервисов для комнаты и пытаетесь зафиксировать транзакцию, вы получите исключение ORA-12008 (ошибка в пути обновления материализованного представления).

Я предполагаю, что RoomServices:

• а) представляет собой небольшую таблицу, которая не подвергается интенсивной модификации
• б) никогда не будет комнаты с более чем 3 услугами

Примечание: вы говорите "более 3 служб", но ваш код говорит "более 2 служб". Поэтому я буду использовать «более двух сервисов».

Тогда как насчет использования триггера оператора?

CREATE OR REPLACE TRIGGER RoomServiceLimit
  AFTER INSERT OR UPDATE ON RoomServices
DECLARE
    badRoomsCount NUMBER;
    badRoomsList VARCHAR2(32767); -- adjust the varchar2 size according to your requirements
BEGIN
    SELECT COUNT(*), LISTAGG(roomNumber, ', ') WITHIN GROUP (ORDER BY 1) 
      INTO badRoomsCount, badRoomsList
      FROM (SELECT roomNumber FROM RoomServices GROUP BY roomNumber HAVING COUNT(*) > 2);
    IF (badRoomsCount > 0) THEN
        RAISE_APPLICATION_ERROR(-20001,'Room/s '||badRoomsList||' will have more than 2 services.');
    END IF;
END;
/

Если RoomServices мал, но имеет слишком много изменений (вставок или обновлений), вы можете рассмотреть возможность создания индекса для RoomNumber.

Если мои предположения ложны, попробуйте что-то вроде:

CREATE GLOBAL TEMPORARY TABLE RoomServicesAux as SELECT roomNumber FROM RoomServices WHERE 1=0;
/

CREATE OR REPLACE TRIGGER PreRoomServiceLimit
  BEFORE INSERT OR UPDATE ON RoomServices
BEGIN
  DELETE FROM RoomServicesAux;
END;
/

CREATE OR REPLACE TRIGGER RowRoomServiceLimit
  BEFORE INSERT OR UPDATE OF roomNumber ON RoomServices FOR EACH ROW
BEGIN
  INSERT INTO RoomServicesAux VALUES (:NEW.roomNumber);
END;
/

CREATE OR REPLACE TRIGGER RoomServiceLimit
  AFTER INSERT OR UPDATE ON RoomServices
DECLARE
    badRoomsCount NUMBER;
    badRoomsList VARCHAR2(32767); -- adjust the varchar2 size according to your requirements      
BEGIN
    SELECT COUNT(*), LISTAGG(roomNumber, ', ') WITHIN GROUP (ORDER BY 1) 
      INTO badRoomsCount, badRoomsList
      FROM (
          SELECT roomNumber 
            FROM RoomServices 
            WHERE roomNumber in (SELECT roomNumber FROM RoomServicesAux) 
            GROUP BY roomNumber 
            HAVING COUNT(*) > 2
           );
    DELETE FROM RoomServicesAux;
    IF (badRoomsCount > 0) THEN
        RAISE_APPLICATION_ERROR(-20001,'Room/s '||badRoomsList||' will have more than 2 services.');
    END IF;
END;
/

Или, если у вас есть Oracle 11g или выше, вы можете использовать составной триггер:

CREATE OR REPLACE TYPE RoomsListType IS TABLE OF INTEGER; -- change to the type of RoomServices.rowNumber
/

CREATE OR REPLACE TRIGGER RoomServiceLimit
  FOR INSERT OR UPDATE OF roomNumber ON RoomServices
COMPOUND TRIGGER
  RoomsList RoomsListType := RoomsListType();
  badRoomsCount NUMBER;
  badRoomsList VARCHAR2(32767); -- adjust the varchar2 size according to your requirements      
AFTER EACH ROW IS 
  BEGIN
    RoomsList.EXTEND;
    RoomsList(RoomsList.COUNT) := :NEW.roomNumber;
  END AFTER EACH ROW;
AFTER STATEMENT IS
  BEGIN
    SELECT COUNT(*), LISTAGG(roomNumber, ', ') WITHIN GROUP (ORDER BY 1) 
      INTO badRoomsCount, badRoomsList
      FROM (
          SELECT roomNumber 
            FROM RoomServices 
            WHERE roomNumber in (SELECT * FROM table(RoomsList))
            GROUP BY roomNumber 
            HAVING COUNT(*) > 2
           );        
    IF (badRoomsCount > 0) THEN
        RAISE_APPLICATION_ERROR(-20001,'Room/s '||badRoomsList||' will have more than 2 services.');
    END IF;
  END AFTER STATEMENT;
END;
/

Кажется, вы не можете решить эту проблему без некоторых обходных путей. Если вы не можете найти ничего лучше, проверьте это:

Я думаю, у вас есть таблица Room, в противном случае создайте ее:

alter table Room add (
  servicesCount integer default 0 not null check (servicesCount <= 3)
);

Затем обновите это число текущими значениями (не уверен, что утверждение верно, здесь это не ключевой момент)

update Room r
set servicesCount = (select count(*)
                     from RoomServices s
                     where r.roomNumber = s.roomNumber);

затем в вашем триггере

create trigger RoomServiceLimit
before insert or update on RoomServices
for each row
begin
  update Room
  set servicesCount = servicesCount + 1
  where roomNumber = :new.roomNumber;
end;

Выглядит довольно некрасиво, но, как я уже сказал, я не уверен, что с триггером можно найти что-то лучше.

EDIT Полный рабочий пример

drop table Room;
drop table RoomServices;

create table Room (
  roomNumber integer primary key,
  servicesCount integer default 0 not null check (servicesCount <= 3)
);

create table RoomServices (
  roomNumber integer,
  service varchar2(100),
  comments varchar2(4000)
);

create trigger RoomServiceLimit
before insert or update or delete on RoomServices
for each row
begin
  if inserting then
    update Room
    set servicesCount = servicesCount + 1
    where roomNumber = :new.roomNumber;
  elsif updating and :old.roomNumber != :new.roomNumber then
    update Room
    set servicesCount = servicesCount + 1
    where roomNumber = :new.roomNumber;

    update Room
    set servicesCount = servicesCount - 1
    where roomNumber = :old.roomNumber;
  elsif deleting then
    update Room
    set servicesCount = servicesCount - 1
    where roomNumber = :old.roomNumber;
  end if;
end;
/

insert into Room(roomNumber) values (1);
insert into Room(roomNumber) values (2);

insert into RoomServices(roomNumber,service,comments) values (1,'cleaning','first');
insert into RoomServices(roomNumber,service,comments) values (1,'drying','second');
insert into RoomServices(roomNumber,service,comments) values (1,'watering','third');
insert into RoomServices(roomNumber,service,comments) values (1,'something','third'); -- error

select * from room;

insert into RoomServices(roomNumber,service,comments) values (2,'something','2: first'); 

update RoomServices
set comments = null
where roomNumber = 2;

select * from room;

update RoomServices -- error
set roomNumber = 1
where roomNumber = 2;

select * from room;

delete from RoomServices where roomNumber = 1;

select * from room;