Триггерные программы что это

Содержание

Зачем компании используют триггерные коммуникации и нужно ли вам общаться с клиентами так же

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

Клиент зашёл на сайт интернет-магазина электроники, подписался на email-рассылку ради скидки 10% на первую покупку, пересмотрел несколько моделей наушников, положил один товар в корзину — и ушёл. На следующий день ему приходит письмо с предложением заказать эти наушники со скидкой 15%.

Это пример работы триггерных email-рассылок. Их отправляют при наступлении триггера: клиент совершает определённые действие — и получает от компании индивидуальное предложение. Триггерные коммуникации могут быть в форме смс, email-рассылок, пуш-уведомлений и любых других способов связи с аудиторией.

Что такое триггерные точки? От чего возникают? Как лечить?

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

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

А городской жительнице 25–40 лет, которая интересуется предметами интерьера, можно предложить дизайнерский пуфик или постельное бельё по особой цене.

Такой подход повышает конверсию и средний чек — если точно попадает в интересы конкретного человека.

Анализируй и зарабатывай: как устроены триггерные коммуникации

Логика триггерных коммуникаций основана на анализе действий пользователя: сколько времени он проводит на вашем сайте и в приложениях, чем пользуется, что покупает и как часто, а также многое другое. Всё это помогает выявить паттерны и шаблоны в поведении целевой аудитории, определить новые сегменты клиентов. В зависимости от полученных данных вы можете сделать клиенту более правильное «триггерное предложение» — такое, которое с большой долей вероятности ему интересно. Такой подход применяют в компании Puzzle English — это онлайн-платформа для самостоятельного изучения английского языка. Так, в Puzzle English применяют триггерные email-рассылки, пуши в приложениях и прочие виды индивидуальной коммуникации с пользователем.

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

— рассказывает генеральный директор Puzzle English Иван Штанько.

Триггерные точки — причина 80% болей. Как с ними бороться?

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

— рассказывает генеральный директор Puzzle English Иван Штанько.

Общие рассылки сразу на всю базу клиентов с одинаковыми креативами и УТП (уникальным торговым предложением — Прим. редакции) работают хуже, считает Иван. Конверсия в оплату будет низкой. Если же не рассказывать сразу обо всём, а предлагать один интересный клиенту продукт со скидкой — это сработает эффективнее.

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

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

CRM24: удобная система управления бизнесом за 0 ₽

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

CRM 24 бесплатна, если хранить в облаке до 5 Гб информации, использовать один вариант воронки продаж и отправлять до 1000 писем в месяц. Количество пользователей системой не ограничено.

Продавать или вовлекать? Два вида триггерных рассылок

Триггерные рассылки делят на продуктовые и маркетинговые. Маркетинговые нацелены на продажи, продуктовые — на вовлечение клиентов. Чем лучше вовлечённость, тем больше продаж и выше чек. Например, человек понажимал разные кнопки в сервисе и ушёл, ничего не купив. Пока нет смысла ему что-то продавать, его надо вовлекать — рассказывать, какие есть ещё продукты и как они работают.

Чтобы решиться на покупку, пользователю надо увидеть спектр возможностей.

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

«Раньше мы думали, что можно один раз выстроить триггерное дерево для пользователей — и жить дальше. Но нет. Над этим нужно работать всё время. Параметры и составляющие любого касания с пользователем должны основываться на выводах из анализа поведения, а не на галлюцинациях»,

— говорит Иван Штанько.

«Раньше мы думали, что можно один раз выстроить триггерное дерево для пользователей — и жить дальше. Но нет. Над этим нужно работать всё время. Параметры и составляющие любого касания с пользователем должны быть основаны на выводах из анализа поведения, а не на галлюцинациях»,

— говорит Иван Штанько.

Если эффективная триггерная цепочка даёт сбои, проблема может быть как в аналитике, так и в формировании выводов. Иногда надо вернуться назад и понять, что в вашем сервисе стало работать хуже — и реформировать это. Сделать скидку побольше? Предложить урок в подарок? Изменить УТП продукта?

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

Готовое решение или инхаус-команда? Выбираем систему для триггерных коммуникаций

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

Например, в базе Puzzle English почти 10 млн пользователей, более трети из которых активно пользуются её продуктами. Компания для формирования продуктовых и рекламных триггерных коммуникаций использует данные двух сервисов: Amplitude для анализа пользовательского поведения на платформе и Exponea для анализа и автоматизации маркетинга. Стоимость использования платформ завязана на количестве данных о пользователях, которые нужно получать и хранить. Для крупных компаний сервисы могут стоить десятки тысяч долларов в год. Для отправки коммуникаций используют Sendsay — сервис для рассылок по e-mail, СМС, push-уведомлений в браузере и соцсетям, который интегрируется с Exponea.

4.3. Триггеры и хранимые процедуры Триггеры и их назначение

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

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

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

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

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

необязательный список локальных переменных с указанием их типов;
программный блок на языке процедур и триггеров InterBase (набор инструкций в операторных скобках BEGIN …END). Программный блок выполняется при включении триггера. Блок может включать в себя другие блоки.

Хранимые процедуры и их назначение

Модульность проектирования. Приложения, которые обращаются к одной базе данных, могут совместно использовать хранимые процедуры, устраняя двойной код, уменьшая размер приложений и устраняя потенциальные ошибки.
Локализация изменений. Если процедура модифицируется, то все внесенные изменения автоматически отражаются во всех приложениях, которые используют процедуру, обеспечивая их согласованность. При этом нет необходимости в перетрансляции и перекомпоновке приложений.
Ускорение обработки. Хранимые процедуры выполняются сервером, а не клиентом, что позволяет ускорить обработку запросов и сократить сетевой трафик.

Источник: studfile.net

SQL-Ex blog

Руководство по триггерам в SQL: настройка отслеживания базы данных в PostgreSQL

Добавил Sergey Moiseenko on Суббота, 3 апреля. 2021

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

В мире стартапов с открытыми исходными кодами и разработкой полного стека (типа Django, Rails, Javascript, PHP, MySQL, Postgres. ) очень популярны ORM, а средства типа триггеров SQL не так востребованы.

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

В этой статье я поделюсь информацией о том, как эффективно использовать триггеры SQL.

Что такое триггер?

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

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

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

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

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

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

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

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

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

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

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

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

Тип события триггера
До или после события
Воздействие триггера

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

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

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

Если одним из перечисленных событий является 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 прохода, который был изменен.
Время прохода до того, как было сделано обновление.

alter table time_punch
add column change_employee_id int null references employee(id);

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

После того, как произойдет обновление таблицы time_punch, выполнится этот триггер и запишет OLD (старое) значение времени прохода в нашу таблицу аудита.

create or replace function fn_change_time_punch_audit() returns trigger as $psql$
begin
insert into time_punch_audit (change_time, change_employee_id, time_punch_id, punch_time)
values
(now(), new.change_employee_id, new.id, old.punch_time);
return new;
end;
$psql$ language plpgsql;

create trigger change_time_punch_audit after update on time_punch
for each row execute procedure fn_change_time_punch_audit();

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

Для триггера на обновление объект NEW представляет те значения, которые будут содержаться
в строке при успешном обновлении. Вы можете использовать триггер для «перехвата» вставки или обновления простым присвоением своих собственных значений в объект NEW. Объект OLD содержит значения строки до обновления.

Проверим, работает ли это! Я добавил второго пользователя с именем Daniel, который будет редактором времени прохода Bear.

select punch_time
from time_punch
where />
punch_time
———————
2020-01-01 11:30:00
(1 row)

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

update time_punch
set punch_time = punch_time + interval ‘5 minute’, change_employee_id = 2
where /> А вот таблица аудита, отражающая прошлые времена прохода:

Дополнительные сообщажения относительно триггеров

Обслуживание триггеров с течением времени.
Связанная логика триггера.
Опыт разработчиков.

Обслуживание триггеров с течением времени

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

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

Связанная логика триггера

Триггеры также могут запускать другие триггеры, быстро усложняя результаты казалось бы невинных INSERT или UPDATE. Этот риск также может привести к побочным эффектам кода приложения.

Опыт разработчиков

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

Изначально SQL может показаться неуклюжим и неудобным языком для изучения, поскольку многие из шаблонов, которые вы изучаете для построения запроса, «вывернуты наизнанку» по сравнению с тем, как вы извлекаете данные на процедурном языке.

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

Обратные ссылки

Нет обратных ссылок

В самом начале мы формируем полный список событий, которые нам когда-либо могут понадобиться. Сюда можно включать даже совсем мелкие эвенты, вроде ошибок заполнения полей профиля. Заметьте, мы не отправляем их сразу в аналитику, а просто составляем перечень. Это можно сделать где-нибудь в Google Sheets или любой другом табличном редакторе.

§ Шаг 2. Присваивание идентификаторов

Затем мы присваиваем каждому событию внутренний идентификатор. Важно, чтобы такие идентификаторы были действительно уникальными для каждого эвента. Желательно также, чтобы они собирались по некоторой схеме – так будет проще потом с ними работать.

Опытным аналитикам эта работа во многом уже знакома, но я всё равно о ней расскажу.

Первым указывается инициатор события. Это могут быть, например: «посетитель», «покупатель» или «система».
Затем добавляется категория самого события. Чаще всего, это объект или раздел, с которым произошло взаимодействие: «статья»,«видео» и тп.
Далее указывается название самого действия (например, «шэринг через соцсети» или «просмотр»).
После этого можно добавить ярлык – какую-то особенность события, если оно, к примеру, может быть выполнено несколькими способами или касаться разных типовых объектов.
Иногда в конце нужно установить значение. Это числовой параметр, как правило – динамический. Например: «id поста» или «время загрузки видео».
Все идентификаторы пишутся на английском языке в нижнем регистре;
Метки разделяются нижним подчёркиванием, а слова внутри самих меток – с помощью тире.

Пример составленного таким образом идентификатора:

Пример структуры события шэринга

§ Шаг 3. Приоритизация списка

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

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

Пока что всё знакомо и понятно, да? Первые три шага в той или иной мере выполняет каждый аналитик. Интересное начинается на шаге четвёртом.

§ Шаг 4. Работа с кодом и конфигами

Итак, у нас есть на руках отсортированная по важности таблица со списком событий-триггеров. Мы понимаем, в какой момент мы шлём каждое, у всех уникальные и логично сформированные идентификаторы. Самое время обратиться к разработчикам.

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

Разработчикам остаётся повесить эту функцию на все указанные в таблице триггеры, а рядышком положить файлик конфигурации со списком «активных» событий, на которые нужно реагировать – именно к нему будет обращаться функция, когда потребуется проверить, надо ли отправлять эвент.

Схема запуска триггерной функции для отправки данных в аналитику

§ Шаг 5. Добавление механизма обновления

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

§ Вместо эпилога

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

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

Источник: sherer.pro

PostgreSQL-триггеры: создание, удаление, примеры

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

Триггер должен быть связан с указанной таблицей, представлением (псевдотаблицей) или внешней таблицей. Он запускает свою часть кода только при выполнении операций с этой сущностью — INSERT, UPDATE, DELETE или TRUNCATE. В зависимости от требований мы можем запускать триггер до, после или вместо события/операции.

Типы триггеров

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

Если триггер помечен опцией FOR EACH ROW, тогда функция вызывается для каждой строки, которая изменяется в результате события. Например, если сделать UPDATE для 100 строк, триггерная функция UPDATE будет вызываться 100 раз, по одному разу для каждой обновлённой строки.

Опция FOR EACH STATEMENT вызовет функцию только один раз для каждого оператора, независимо от количества изменяемых строк.

Использование триггеров

Это довольно мощный инструмент, у которого много сценариев использования. Вот лишь несколько примеров:

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

Триггеры помогают оптимизировать количество запросов. Например, у вас на сервере Timeweb Cloud есть таблица, в которую записываются временные метки. Задача — агрегировать данные за указанные интервалы (пусть их будет четыре в сутки, каждый продолжительностью 6 часов).

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

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

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

Создание триггера

С практической пользой от использования разобрались. Теперь посмотрим, как создать триггер в PostgreSQL.

Синтаксис запроса следующий:

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name < BEFORE | AFTER | INSTEAD OF >< event [ OR . ] >
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING < < OLD | NEW >TABLE [ AS ] transition_relation_name > [ . ] ]
[ FOR [ EACH ] < ROW | STATEMENT >]
[ WHEN ( condition ) ]
EXECUTE < FUNCTION | PROCEDURE >function_name ( arguments )

где событие (event) может быть одним из следующих:

INSERT
UPDATE [ OF column_name [, . ] ]
DELETE
TRUNCATE

Здесь требуется несколько пояснений.

Вы можете создать (CREATE) или заменить (REPLACE) уже существующий триггер.
Вы сразу связываете функцию с конкретной таблицей, представлением или внешней таблицей. Код будет исполняться только при наступлении события с этой связанной сущностью.
Триггеры с опцией INSTEAD OF должны быть помечены опцией FOR EACH ROW и могут быть определены только в представлениях. Триггеры, которые выполняются до (BEFORE) или после события (AFTER) в представлении должны быть помечены как FOR EACH STATEMENT. В документации есть таблица, которая поможет сориентироваться.

Простые примеры

Чтобы разобраться с синтаксисом, посмотрим на примеры триггеров PostgreSQL.

Например, здесь вы говорите движку, что нужно выполнять функцию check_account_update() каждый раз до обновления таблицы accounts:

CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();

В этом примере вы устанавливаете дополнительное условие. Функция должна выполняться только в том случае, если обновляется столбец balance в таблице accounts.

CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();

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

CREATE TRIGGER log_update
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_account_update();

Ещё один пример — с INSTEAD OF. Функция view_insert_row() выполняется для каждой строки, чтобы вставить строки в таблицы, лежащие в основе представления:

CREATE TRIGGER view_insert
INSTEAD OF INSERT ON my_view
FOR EACH ROW
EXECUTE FUNCTION view_insert_row();

Триггер на удаление в PostgreSQL можно добавить к транзакциям, удаляющим записи:

CREATE TRIGGER example_delete_trigger
AFTER DELETE ON my_view
FOR EACH ROW
EXECUTE PROCEDURE aft_delete();

Практика — добавление информации в две таблицы

Давайте рассмотрим пример создания триггера PostgreSQL , который будет добавлять в таблицу информацию о новом сотруднике, если эти данные появились в другой таблице.

Сначала нужно создать обе таблицы:

CREATE TABLE «Employee»
(
«EmployeeId» INT NOT NULL,
«LastName» VARCHAR(20) NOT NULL,
«FirstName» VARCHAR(20) NOT NULL,
«Title» VARCHAR(30),
«ReportsTo» INT,
«BirthDate» TIMESTAMP,
«HireDate» TIMESTAMP,
«Address» VARCHAR(70),
«City» VARCHAR(40),
«State» VARCHAR(40),
«Country» VARCHAR(40),
«PostalCode» VARCHAR(10),
«Phone» VARCHAR(24),
«Fax» VARCHAR(24),
«Email» VARCHAR(60),
CONSTRAINT «PK_Employee» PRIMARY KEY («EmployeeId»)
);

CREATE TABLE «Employee_Audit»
(
«EmployeeId» INT NOT NULL,
«LastName» VARCHAR(20) NOT NULL,
«FirstName» VARCHAR(20) NOT NULL,
«UserName» VARCHAR(20) NOT NULL,
«EmpAdditionTime» VARCHAR(20) NOT NULL,
);

Таблицы готовы, теперь нужно добавить триггерную функцию, чтобы настроить между ними обмен данными по наступлению события. В нашем случае событие — это добавление информации о новом сотруднике в таблицу «Employee».

CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc()
RETURNS trigger AS
$$
BEGIN
INSERT INTO «Employee_Audit» ( «EmployeeId», «LastName», «FirstName»,»UserName» ,»EmpAdditionTime»)
VALUES(NEW.»EmployeeId»,NEW.»LastName»,NEW.»FirstName»,current_user,current_date);
RETURN NEW;
END;
$$
LANGUAGE ‘plpgsql’;
CREATE TRIGGER employee_insert_trigger
AFTER INSERT
ON «Employee»
FOR EACH ROW
EXECUTE PROCEDURE employee_insert_trigger_fnc();

Как только мы выполним описанный выше INSERT в «Employee», триггер добавит одну новую запись в «Employee_Audit» со следующими данными:

Теперь проверим, что всё работает так, как мы предполагали. Сначала выведем сведения о сотруднике из таблицы «Employee», в которую мы только что вставили данные:

Теперь посмотрим, записались ли нужные данные в таблицу «Employee_Audit»:

Отлично, всё работает!

Изменение триггера

Чтобы изменить свойства триггера, используйте CREATE OR REPLACE TRIGGER, указав имя существующей триггерной функции и связанную таблицу. Остальные свойства вы можете менять так, как нужно для выполнения вашей задачи.

Вы также можете переименовать триггер. Для этого используйте запрос ALTER TRIGGER:

ALTER TRIGGER name ON table_name RENAME TO new_name

Удаление триггера

Используйте DROP TRIGGER, чтобы удалить триггер PostgreSQL . Синтаксис очень простой:

DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

Например, так вы удалите some_example_of_trigger, связанный с таблицей Example:

DROP TRIGGER some_example_of_trigger ON «Example» ;

Чтобы в PostgreSQL отключить триггер, пользователь должен быть владельцем таблицы.

Можно использовать дополнительные параметры при отключении:

IF EXISTS — указание на то, что не надо выдавать ошибку, если такого триггера нет.
CASCADE — автоматически удалять все объекты, которые зависят от триггера, объекты, которые зависят от этих объектов, и так далее.
RESTRICT — не отключать триггер, если от него зависят другие объекты. Это значение по умолчанию.

Важные моменты, которые следует помнить

Чтобы создать триггер, пользователь должен иметь привилегию TRIGGER для таблицы и привилегию EXECUTE для функции.
Вы можете проверить системный каталог «pg_trigger» на наличие существующей информации о триггерах в базе данных.
Если вы создадите несколько триггеров для одного и того же объекта и для одного и того же события, они будут срабатывать в алфавитном порядке по имени.

В своем официальном канале Timeweb Cloud собрали комьюнити из специалистов, которые говорят про IT-тренды, делятся полезными инструкциями и даже приглашают к себе работать.

Источник: timeweb.cloud