Стасышина
Татьяна Леонидовна
Для студентов
Педагогическая деятельность
Научная деятельность
pk@nstu.ru, +7 (383) 319 59 99 — приёмная комиссия

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 real
run 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.
Триггеры не должны быть бессмысленными и бесполезными.
Наверх
 

Обработка персональных данных

Мы используем сервис веб-аналитики Яндекс Метрика, который использует cookie.

Собранная при помощи cookie информация не может идентифицировать вас, однако может помочь нам улучшить работу нашего сайта. Вы можете отказаться от использования cookies, выбрав соответствующие настройки в браузере. Также Вы можете запретить сбор данных с помощью расширения для браузера «Блокировщик Яндекс Метрики». Используя этот сайт, вы соглашаетесь на обработку персональных данных.