PL/SQL перед триггером вставки, чтобы запретить вставку двух записей, возраст которых превышает 10 лет

Question

ПЕРЕД ВСТАВИТЬ ТРИГГЕР должен сделать следующее:

• запрещает размещение двух пациентов в одной комнате, где разница в возрасте превышает 10 лет.
• Кроме того, триггер должен также запретить двум пациентам с допустимым возрастом (меньше или равным 10) назначаться на одну кровать.

Это означает:

• Если новый пациент на 10 лет больше, чем любой уже принятый пациент, он не может находиться в одной комнате.
• Если новый пациент находится на расстоянии менее 10 лет от любого уже принятого пациента, он может находиться в той же комнате, но не в одной постели.

Структура стола пациента:

   PAT_ID         CHAR
   PAT_NAME       CHAR
   PAT_GENDER     CHAR
   PAT_AGE        NUMBER
   PAT_ADMIT_D    CHAR
   PAT_WING       CHAR
   PAT_ROOM#      NUMBER
   PAT_BED        CHAR

Пока что мой код:

   CREATE OR REPLACE TRIGGER assignmentcheck 
   before insert on patient
   for each row

   declare
        cursor p1_cursor is
             select pat_age, pat_room#, pat_bed from patient;

   agediff number;
   begin

        for p1_pat in p1_cursor loop
        agediff :=  p1_pat.pat_age - :new.pat_age;
        if (agediff >10) then
             if (:new.pat_room# = p1_pat.pat_room#) then
             raise_application_error(-20001, 'Age difference greater than 10     cannot be in the same room');
        end if;
        else if (:new.pat_room# = p1_pat.pat_room#) and (:new.pat_bed =  p1_pat.pat_bed) then
        raise_application_error(-20002, 'Age difference less than 10 cannot be on the same bed');
        end if;
        end if;
  end loop;

 end;
 /

Некоторый тест sql:

 insert into patient values ('AZ24523', 'Zhou, Alicia', 'F', 24, '14-APR-2015', 'A', 20, 'B');
 insert into patient values ('JA33234', 'Abbott, John', 'M', 50, '14-APR-2015', 'A', 16, 'B');
 insert into patient values ('AN32676', 'Newman, Andrew', 'M', 10, '14-APR-2015', 'A', 16, 'B');
 insert into patient values ('ZZ24523', 'Zhang, Zhaoping', 'F', 38, '14-APR-2015', 'A', 16, 'A');

Триггер предполагает сравнение новой строки со строками, уже существовавшими в таблице. Но до сих пор мой триггер сравнивал только новые строки, а не те, которые уже есть в таблице.

Как мне заставить это работать как ожидалось? Я искал везде, но не могу найти в любом случае, чтобы сравнить со всей таблицей / базой данных.

Большое спасибо.

Answer 1

Хорошие новости: ваш триггер работает (не полностью, но почти).

Я не знаю, что вы подразумеваете под: Но пока мой триггер сравнивал только новые строки, а не те, которые уже есть в таблице. Ваш триггер явно проверяет уже существующие строки в таблице. Работает нормально. Конечно, если вы создаете таблицу, вставляете несколько конфликтующих строк, и при следующем создании триггера он не будет исправлять предыдущие вставки - их нужно удалить другим способом (удалить или обновить).

Плохая новость: ваш триггер не будет работать, если вы попытаетесь вставить несколько строк в один выбор, как здесь, вы получите ошибку SQL Error: ORA-04091: table CHRIS.PATIENT is mutating, trigger/function may not see it:

insert into patient  
  select 'AZ24523', 'Zhou',   'F', 24, '2015-04-14', 'A', 20, 'B' from dual union all
  select 'JA33234', 'Abbott', 'M', 50, '2015-04-14', 'A', 16, 'B' from dual union all
  select 'AN32676', 'Newman', 'M', 10, '2015-04-14', 'A', 16, 'B' from dual union all
  select 'ZZ24523', 'Zhang',  'F', 38, '2015-04-14', 'A', 16, 'A' from dual;

Что можно сделать в этой ситуации? В Oracle 11g вы можете изменить свой код на составной триггер:

create or replace trigger assignmentcheck 
for insert on patient compound trigger

  type patients_t is table of patient%rowtype;
  v_patients patients_t := patients_t();
  v_patient patient%rowtype;

before statement is begin
  select * bulk collect into v_patients from patient; 
end before statement;

before each row is begin
  v_patients.extend;
  v_patients(v_patients.last).pat_id := :new.pat_id;
  v_patients(v_patients.last).pat_age := :new.pat_age;
  v_patients(v_patients.last).pat_room# := :new.pat_room#;
  v_patients(v_patients.last).pat_bed := :new.pat_bed;
end before each row;

after each row is begin
  for i in 1..v_patients.count() loop
    v_patient := v_patients(i);
    if v_patient.pat_id<>:new.pat_id then 
      if v_patient.pat_room# = :new.pat_room# then 
        if abs(v_patient.pat_age-:new.pat_age) > 10 then 
          raise_application_error(-20001, 'Age difference to big');
        elsif v_patient.pat_bed = :new.pat_bed then
          raise_application_error(-20002, 'Bed already taken');
        end if;
      end if;
    end if;
  end loop;
end after each row;

end assignmentcheck;

В предыдущей версии Oracle вы можете использовать одно из решений, обсуждаемых в этой статье: исключения из таблицы мутаций.

Answer 2

Реляционные базы данных позволяют легко определить отношения 1-1, отношения 1-многие и многие-многие. Варианты выбора: ноль, один или любое число больше, чем один. Не так просто определить, скажем, 1-2 отношения.

Вот одна идея. Сначала таблица, чтобы определить каждую комнату. Существует одна запись для каждой комнаты.

create table Rooms(
    ID    int  identity,
    ... -- other room-related data
    constraint PK_Rooms primary key( ID )
);

Далее таблица, чтобы определить кровати в каждой комнате. Существует одна запись для каждой кровати в каждой комнате.

create table Room_Beds(
    RoomID int not null,
    BedID  smallint not null,
    constraint PK_Room_Beds primary key( RoomID, BedID ),
    constraint FK_Room_Beds_Room foreign key( RoomID )
        references Beds( ID )
);

таблицы Rooms а также Room_Bedsпосле определения стабильны. Их содержимое остается неизменным во время ежедневных операций. Следующая таблица хранит полную историю использования кровати. Запись вставляется, когда она занята, и другая, когда она становится незанятой. Обратите внимание, что запись для BedID 3 (или выше) не может быть введена, если только две кровати были определены для комнаты.

create table Bed_Patient(
    RoomID     int not null,
    BedID      smallint not null,
    StartDate  date not null,
    PatientID  int,
    IsOccupied as case when PatientID is null then 'N' else 'Y' end,
    constraint PK_Bed_Patient primary key( RoomID, BedID, StartDate )
);

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

create view Available_Beds as
select  rb.*
from    Room_Beds rb
join    Bed_Patient bp
    on  bp.RoomID = rb.ID
    and bp.IsOccupied = 'N'
    and bp.StartDate =(
        select  Max( Start_Date )
        from    Bed_Patient
        where   RoomID = bp.RoomID
            and BedID  = bp.BedID );

Теперь триггер вставки включен Bed_Patient можете проверить, что кровать указана в представлении, если PatientID не равен нулю, или нет в списке, если PatientID равен нулю.

Сроки должны быть обработаны, но это в значительной степени дано для большинства приложений. Этому помогает тот факт, что, как только строка вставлена ​​в Bed_Patient с ненулевым значением в поле PatientID, эта комната больше не отображается в Available_Beds Посмотреть.