Файл: Лекция 11. Функции языка sql. Создание функции в pl pgsql. Триггеры. Функции.docx

Триггеры SQL, также называемые триггерами баз данных, позволяют вам сказать движку SQL (например PostgreSQL) выполнить часть кода при наступлении некоторого события, или даже перед наступлением события.

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

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

Преимущества использования триггеров SQL

Поддержание целостности данных

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

Разделение бизнес-логики

Размещение критичной бизнес-логики в коде приложения также представляет проблему, когда бизнес-логика обновляется. Если вашим бизнес-требованием являлось умножение входящих номеров на 10, а теперь вы захотели умножать это число на 20, изменение логики в SQL гарантировало бы, что каждые данные, начиная точно с момента развертывания, будут обрабатываться новой логикой.

Сервер SQL действует как единственная точка истины. Если логика внедрена на множестве серверов приложений, вы не сможете ожидать определенного изменения в поведении, выполненного чисто.

Атомарные транзакции

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

Как создать триггер SQL: синтаксис PostgreSQL

Вот составляющие создания триггера для вашей базы данных:

1. 
   Тип события триггера
   

2. 
   До или после события
   

3. 
   Воздействие триггера
   

Типы событий триггера

---

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

Триггер базы данных допускает также перечисление более одного из этих событий.

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

Триггер BEFORE (до) или AFTER (после)

Триггер может выполняться либо до, либо после события.

Если вы хотите заблокировать событие типа INSERT, вы захотите выполнять действие до (BEFORE). Если вы хотите быть уверенным, что событие действительно произойдет, идеальный вариант - после (AFTER).

Воздействие триггера

Триггер может выполняться либо на строку, либо на оператор. Скажем, вы выполняете один оператор UPDATE, который изменяет 5 строк в таблице.

Если вы укажете в триггере FOR EACH ROW, тогда триггер выполнится 5 раз. Если вы укажете FOR EACH STATEMENT, тогда он выполнится только раз.

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

Пример триггера #1: создание таймера

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

Настройка схемы базы данных

Структура схемы предполагает каждый вход и выход отдельными событиями. Каждое событие - это строка в таблице time_punch. Как альтернативу вы можете также сделать каждую "рабочую смену" сотрудника событием и хранить время как входа, так и выхода в одной строке.

В следующей статье я глубже погружусь в разработку схемы базы данных.

Для нашего примера я уже разработал схему таблиц. Нижеприведенный код создает таблицы employee и time_punch и вставляет некоторые данные по времени прохода для нового сотрудника Bear.

create table employee ( id serial primary key, username varchar );

create table time_punch (

---

id serial primary key,

employee_id int not null references employee(id),

is_out_punch boolean not null default false,

punch_time timestamp not null default now()

);

insert into employee (username) values ('Bear');

insert into time_punch (employee_id, is_out_punch, punch_time)

values

(1, false, '2020-01-01 10:00:00'),

(1, true, '2020-01-01 11:30:00');


Bear зашел в 10:00 и вышел в 11:30 (длинный рабочий день). Давайте напишем запрос SQL для вычисления рабочего времени Bear.

Остановитесь и подумайте, как бы вы решили эту задачу при данной схеме и с помощью одного SQL.

Использование SQL для вычисления рабочего времени

Решение, которое я предлагаю, ищет на каждый "выход" соответствующий ему "вход".

select tp1.punch_time - tp2.punch_time as time_worked

from time_punch tp1

join time_punch tp2

on tp2.id = (

select tps.id

from time_punch tps

where tps.id < tp1.id

and tps.employee_id = tp1.employee_id

and not tps.is_out_punch

order by tps.id desc limit 1

)

where tp1.employee_id = 1 and tp1.is_out_punch;

time_worked

2

-------------

3

01:30:00 (1 row)


В этом запросе я выбираю все выходы, затем я соединяю их с наиболее близким "входом". Беру разность временных меток и получаю количество часов, которое отработал Bear в каждой смене!

Одна из проблем в этой схеме состоит в том, что возможно вставить несколько "входов" или "выходов" подряд. С созданным запросом это приведет к неоднозначности, которая может привести к неточным расчетам и зарплате сотрудников - тбольше или меньше, чем они должны были бы получить.

Пример триггера INSERT BEFORE: сохранение целостности данных

Нам требуется то, что не позволит нарушить шаблон вход/выход. К сожалению, ограничения check только отслеживают вставляемую или обновляемую строку и не могут учитывать данные из других строк.

Это идеальная ситуация для использования триггера баз данных!

Давайте создадим триггер для предотвращения события INSERT, которое нарушает наш шаблон. Сначала мы создадим "триггерную функцию". Эта функция есть то, что будет выполнять триггер при наступлении события.

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

create or replace function fn_check_time_punch() returns trigger as $psql$

begin

if new.is_out_punch = (

select tps.is_out_punch

from time_punch tps

where tps.employee_id = new.employee_id

order by tps.id desc limit 1

) then

return null;

end if;

return new;

end;

$psql$ language plpgsql;


Ключевое слово new представляет значения вставляемой строки. Это также объект, который вы можете вернуть, чтобы позволить продолжиться вставке. Напротив, возвращение 

---

null остановит вставку.

Этот запрос сначала находит в time_punch предыдущее значение и гарантирует, что это значение входа/выхода не совпадает с вставляемым значением. Если значения совпадают, то триггер возвращает null, и time_punch не записывается. В противном случае, триггер возвращает new и оператор insert продолжается.

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

create trigger check_time_punch before insert on time_punch

for each row execute procedure fn_check_time_punch();


Давайте попробуем вставить еще один "выход":

insert into time_punch (employee_id, is_out_punch, punch_time)

values

(1, true, '2020-01-01 13:00:00');

Output: INSERT 0 0


Как можно видеть по выводу, триггер предотвратил вставку двух последовательных выходов для одного и того же сотрудника.

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

Пример триггера в PostgreSQL #2: создание таблицы аудита

Аккуратное сохранение данных о нахождении сотрудников критично для бизнеса. Данные подобного типа часто непосредственно отражаются на зарплате и, с другой стороны, на заработках компании.

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

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

Я буду использовать нашу таблицу time_punch для демонстрации создания и автоматического обновления таблицы аудита с помощью триггеров.

Создание таблицы аудита

create table time_punch_audit (

id serial primary key,

change_time timestamp not null default now(),

change_employee_id int not null references employee(id),

time_punch_id int not null references time_punch(id),

punch_time timestamp not null

);


В эту таблицу записывается:

• 
  Время обновления прохождения.
  

• 
  Сотрудник, который выполнил обновление.
  

• 
  ID прохода, который был изменен.
  

• 
  Время прохода до того, как было сделано обновление.
  

Прежде чем создавать триггер, сначала нам нужно добавить столбец change_employee_id в таблицу time_punch. Тогда триггер будет знать, какой сотрудник сделал каждое изменение в таблице time_punch.

---

alter table time_punch

add column change_employee_id int null references employee(id);


(Как альтернативное решение без добавления каких-либо столбцов в time_punch, можно аннулировать права update на эту таблицу и заставить пользователей базы данных использовать пользовательскую функцию типа update_time_punch(id, change_user_id, ...))

---