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

Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.

Кратко о задаче

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

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

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

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

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

Триггеры помогают оптимизировать количество запросов. Например, у вас на сервере Simple-Server есть таблица, в которую записываются временные метки. Задача — агрегировать данные за указанные интервалы (пусть их будет четыре в сутки, каждый продолжительностью 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» со следующими данными:

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 — не удалять триггер, если от него зависят другие объекты. Это значение по умолчанию.

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

Нужен сервер для практики? Арендуйте VPS/VDS в России — root-доступ, NVMe, DDoS-защита и поддержка 24/7.

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

Кратко о задаче

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

VPS для проекта

StarterVDS

PerformanceVDS

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

Кратко о задаче

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

VPS для проекта

StarterVDS

PerformanceVDS

Читайте также