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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Подробности смотрите в документации.

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

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

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

Для возможности удаления триггера пользователь должен быть владельцем таблицы.

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

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

Полное описание смотрите в документации.

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

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

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