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.

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

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

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

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

dbaas

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

Чтобы разобраться с синтаксисом, посмотрим на примеры триггеров 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» со следующими данными:

INSERT INTO "Employee" 
VALUES(12,' Smith','Jeff','Editor',1,'1992-05-28 00:00:00','2022-01-15 00:00:00','Paseo de Gracia','Barcelona','Catalonia','Spain','128 665','+15 52-469-2573','+15 52-469-2573','mail@mail.com')

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

SELECT * FROM "Employee" WHERE "EmployeeId" =12;
EmployeeId | 12
LastName   | Smith
FirstName  | Jeff
Title  | Editor
ReportsTo  | 1
BirthDate  | 1992-05-28 00:00:00
HireDate   | 2022-01-15 00:00:00
Address | Paseo de Gracia
City   | Barcelona
State  | Catalonia
Country | Spain
PostalCode | 128 665
Phone  | +15 52-469-2573
Fax    | +15 52-469-2573
Email  | mail@mail.com

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

SELECT * FROM "Employee_Audit" ;
EmployeeId   | 12
LastName    | Smith
FirstName   | Jeff
UserName    | postgres
EmpAdditionTime | 2022-06-17

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

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

Чтобы изменить свойства триггера, используйте 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" ;

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

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

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

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

Подготовили для вас выгодные тарифы на DBaaS

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

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

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