8. Требования к триггерам и примеры

В программном обеспечении информационных системах работающих с базами данных можно выделить три уровня:

- уровень хранения данных;

- уровень обработки данных;

- уровень управления интерфейсом.

Назначение первого уровня – сохранение и выборка данных, а также обеспечение непротиворечивости и целостности данных . Первый уровень представляет собой набор таблиц, а также декларативные и триггерные ограничения целостности, которые в совокупности обеспечивают хранение и непротиворечивость данных. Объекты этого уровня создаются средствами языка SQL и хранятся на сервере СУБД.

Назначение второго уровня – обработка данных в соответствии с бизнес-правилами предметной области. Этот уровень представляет собой набор процедур и функций, которые инкапсулируют элементарные операции с базой данных (SQL-запросы) и обеспечивают соблюдение бизнес-правил.

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

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

Например:

- билет нельзя сдать раньше, чем он был куплен;

- один самолет не может выполнять два рейса одновременно.

Такие ограничения при создании БД реализуются декларативными средствами и триггерами.

Если же существуют обстоятельства ( даже если это очень редкие, исключительные обстоятельства), при которых допустимо нарушение ограничения, такое ограничение следует отнести к бизнес-правилам.

Например: при исключительных обстоятельствах

- на работу может быть принято больше сотрудников, чем указано в штатном расписании;

- пассажирский рейс может быть выполнен грузовым самолетом;

- билет может быть сдан после того, как самолет улетел.

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

Требования к триггерам

Правило 1.

Триггерами должны быть реализованы только те ограничения целостности, которые не могут быть заданы декларативными средствами, т.е.спомощьюограниченийNOT NULL ,UNIQUE, PRIMARY KEY, DEFAULT, CHECKиFOREIGN KEY REFERENCES.

Пример 1. В БД библиотеки имеется таблица «Выдача литературы»

CREATE TABLE delivery1
(id integer PRIMARY KEY,	--код выдачи
id_exemplar integer REFERENCES exemplar(id),	--код экземпляра книги
id_reader integer REFERENCES reader(id),	--код читателя
id_librarian integer REFERENCES librarian(id)	--код библиотекаря
delivery_date date NOT NULL	--дата выдачи
check ( delivery_date<=now()),
return_date date check (return_date <=now()));	--дата возврата

Неправильно

Для таблицы создан триггер проверяющий ограничение: дата возврата книги не может предшествовать дате выдачи.

CREATE FUNCTION tf_delivery_bef_ins_upd () RETURNS SETOF trigger
LANGUAGE plpgsql
AS $$ return
return NEW;

CREATE TRIGGER trg_delivery_bef_ins_upd
    BEFORE INSERT OR UPDATE ON delivery
    FOR EACH ROW
    EXECUTE PROCEDURE tf_delivery_bef_ins_upd();

Это ограничение может быть реализовано декларативными средствами :

ALTER TABLE ONLY delivery
ADD CONSTRAINT ddd CHECK ((delivery_date <= return_date));

Правильно

Для таблицы разработан триггер, который проверяет ограничение: экземпляр не может одновременно находиться более чем у одного читателя.
В терминах БД
Среди строк таблицы delivery с одним и тем же значением id_exemplar:
1. может быть максимум одна строка с пустой датой возврата (return_date is null)
2. не может быть строк с пересечением периодов с delivery_date по return_date.

CREATE FUNCTION tf_delivery_bef_ins_upd ()RETURNS SETOF trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
if (exists (select id
              from delivery t
          where t.id<>new.id
                  and
                  t.id_exemplar = new.id_exemplar
                and
                  t.delivery_date < case when new.return_date is null
                                        then now()
                                      else new.return_date
                                   end
                and  case when t.return_date is null
                  then now()
                    else t.return_date
            end >new.delivery_date
             )
     )
THEN raise exception 'Экземпляр одновременно у двух читателей';
    return NULL;
end if;
return NEW;
END;

CREATE TRIGGER trg_delivery_bef_ins_upd
    BEFORE INSERT OR UPDATE ON delivery
    FOR EACH ROW
    EXECUTE PROCEDURE tf_delivery_bef_ins_upd();

Правило 2.
Триггеры должны быть написаны для всех операций и всех таблиц, которые затрагивает ограничение.

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

Пример 2.
В БД гостиничного комплекса есть таблицы

CREATE TABLE hotel	-- Гостиницы
( id_hotel integer PRIMARY KEY,	-- код гостиницы
name_hotel character(20) NOT NULL,	-- название гостиницы
max_floor integer NOT NULL check( max_floor >0),	-- количество этажей
….…);

CREATE TABLE room	-- Номера
( id_room integer PRIMARY KEY	-- код номера
id_hotel integer REFERENCES Нotel (id_hotel),	-- код гостиницы
number_room character(20) NOT NULL,	-- название/номер комнаты
max_guest integer NOT NULL check(max_guest>0),	-- количество мест
floor integer NOT NULL check(floor >0),	-- этаж
….…);

Неправильно

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

Триггер

CREATE FUNCTION tf_room_bef_ins() RETURNS SETOF trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
   if (new.floor > (select max_floor from hotel t
                where t.id_hotel=new.id_hotel))
       THEN raise exception 'У номера слишком высокий этаж';
   return NULL;
   end if;
   return NEW;
END;
$$;

CREATE TRIGGER trg_room_bef_ins
BEFORE INSERT ON room
FOR EACH ROW
EXECUTE PROCEDURE tf_room_bef_ins();

Правильно.

Ограничение в терминах бизнес-области: ни один гостиничный номер не может располагаться на этаже превышающем этажность гостиницы.
Ограничение в терминах базы данных: значение поля floor_room в строке таблицы room не должно быть больше, чем значение поля max_floor в строке таблицы hotel со значением поля Id_ hotel = room.Id_hotel.
Нарушение ограничения возможно при следующих операциях: вставке и модификации строк таблицы room, модификации строк таблицы hotel. Требуется два триггера:
- перед вставкой или обновлением строки в таблице room.
- перед обновлением строки в таблице hotel.

Триггеры

CREATE FUNCTION tf_room_bef_ins() RETURNS SETOF trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    fl integer;
BEGIN
select max_floor into fl
from hotel
where hotel.id_hotel=new.id_hotel;
if (new.floor > fl)
THEN
    raise exception 'У номера слишком высокий этаж. Максимум - %', fl;
    return NULL;
end if;
return NEW;
END;
$$;

CREATE TRIGGER trg_room_bef_ins
BEFORE INSERT OR UPDATE ON room
FOR EACH ROW
EXECUTE PROCEDURE tf_room_bef_ins();

CREATE FUNCTION tf_hotel_bef_upd() RETURNS SETOF trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
if (exists (select id_room
            from room t
            where t.floor>new.max_floor) )
THEN
       raise exception 'Существует номер с этажом > %',new.max_floor ;
       return NULL;
end if;
return NEW;
END;
$$;

CREATE TRIGGER trg_hotel_bef_upd
BEFORE UPDATE ON hotel
FOR EACH ROW
EXECUTE PROCEDURE tf_hotel_bef_upd();

Вопрос. Почему перед вставкой строки в таблицу Hotel триггер не нужен?

Правило 3.
При нарушении ограничения триггер должен формировать достаточно информативное сообщение об ошибке.

Пример 3.
В БД аэропорта имеется таблица «Фактические полеты»

CREATE TABLE realrun
(id integer PRIMARY KEY,	--код полета
id_flight integer REFERENCES flight (id),	--код рейса
id_plane integer REFERENCES plane (id),	--код самолета
id_team integer REFERENCES team (id),	--код экипажа
begin_time timestamp with time zone NOT NULL check ( begin_time<= cur-rent_timestamp),	--фактические дата-время вылета
end_time timestamp with time zone ,	-- фактические дата-время приземления
……	……
check ( begin_time < end_time ));

Для таблицы разработан триггер, который проверяет ограничение: самолет не может одновременно выполнять более чем один полет.
В терминах БД
Среди строк таблицы realrun с одним и тем же значением id_plane:
1. может быть максимум одна строка с пустым временем приземления (end_time is null)
2. не может быть строк с пересечением периодов с begin_time по end_time.

Неправильно

CREATE FUNCTION tf_realrun_bef_ins_upd ()RETURNS SETOF trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
new_et timestamp;
BEGIN
   new_et:= case when new.end_time is null
                 then current_timestamp + interval'1 hour'
               else new.end_time
       end;
   if (exists (select id
              from realrun t
            where t.id<>new.id
               and    t.id_plane = new.id_plane
                 and    t.begin_time < new_et
               and    case when t.end_time is null
                    then current_timestamp
                    else t.end_time
               end >new.begin_time )
       ) OR
        (exists (select id
            from realrun t
              where t.id<>new.id
                 and    t.id_team = new.id_team
                 and    t.begin_time new_et
               and    case when t.end_time is null
                 then current_timestamp
                 else t.end_time
               end >new.begin_time )
    )
    THEN    raise exception 'ERROR: incorrect data';
                 return NULL;
    end if;
    return NEW;
END;
$$

Правильно

CREATE FUNCTION tf_realrun_bef_ins_upd ()RETURNS SETOF trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
new_et    timestamp;
BEGIN
    new_et:= case when new.end_time is null
             then current_timestamp + interval'1 hour'
                         else new.end_time
          end;
   IF (exists (select id
           from realrun t
             where t.id<>new.id
                    and    t.id_plane = new.id_plane
            and    t.begin_time < new_et
            and    case when t.end_time is null
                    then current_timestamp + interval'1 hour'
                    else t.end_time
            end >new.begin_time
        ) )
     THEN    raise exception 'Самолет не может выполнять более одного полета одновременно';
     IF (exists (select id
              from realrun t
              where t.id<>new.id
                 and    t.id_team = new.id_team
                   and    t.begin_time < new_et
               and    case when t.end_time is null
                    then current_ timestamp + interval'1 hour'
                                              else t.end_time
               end >new.begin_time
                      ))
    THEN    raise exception 'Экипаж не может выполнять более одного полета одновременно';
      return NULL;
     end if;
     return NEW;
END;
$$

Правило 4.
Триггер должен быть реализован таким образом, чтобы минимизировать время его выполнения и другие используемые ресурсы. Одно из следствий этого правила - требование оптимальности функций триггера, они должны быть максиально короткими, простыми и понятным.

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

Правило 6.
Триггеры не должны быть бессмысленными и бесполезными.