Пошук уроків, статей та іншого контенту
Реалізуєте тригери для автоматичних дій під час INSERT, UPDATE і DELETE та розберете типові сценарії.
Тригер — це об’єкт PostgreSQL, який автоматично виконує функцію у відповідь на подію над таблицею або поданням.
Найчастіше тригери використовують для:
ведення аудиту змін;
автоматичного оновлення службових полів;
перевірки складних бізнес-правил;
синхронізації пов’язаних даних;
заборони або перетворення операцій INSERT, UPDATE, DELETE.
Тригер складається з двох частин:
функції-тригера;
визначення тригера, яке вказує, коли і для якої події викликати функцію.
Функція, призначена для тригера, має повертати тип trigger.
PostgreSQL підтримує три основні моменти виконання тригера.
BEFOREВиконується до операції над рядком.
Застосовується, коли потрібно:
змінити значення NEW;
перевірити дані;
скасувати операцію;
автоматично встановити значення полів.
Для BEFORE-тригера можна:
повернути NEW, щоб продовжити операцію;
повернути OLD для DELETE;
повернути NULL, щоб пропустити обробку поточного рядка.
AFTERВиконується після успішної операції.
Застосовується, коли потрібно:
записати інформацію про зміну в журнал;
оновити інші таблиці;
виконати дію, яка не повинна впливати на саму основну операцію.
Значення, повернуте функцією AFTER-тригера, ігнорується. Зазвичай повертають NEW або OLD для зрозумілості.
INSTEAD OFВикористовується переважно для подань (VIEW). Такий тригер замінює стандартну операцію власною логікою.
Тригер може виконуватися для кожного рядка або один раз для всієї SQL-команди.
FOR EACH ROWФункція запускається окремо для кожного зміненого рядка.
Наприклад, команда:
UPDATE products
SET price = price * 1.1;викличе FOR EACH ROW-тригер для кожного рядка таблиці.
У такому тригері доступні спеціальні змінні:
NEW — новий стан рядка;
OLD — попередній стан рядка.
FOR EACH STATEMENTФункція запускається один раз для всієї SQL-команди, незалежно від кількості змінених рядків.
Для такого тригера NEW та OLD недоступні як окремі рядки. Цей режим корисний, коли важливий сам факт виконання команди, а не кожен змінений запис.
Якщо режим не вказано, PostgreSQL використовує FOR EACH STATEMENT.
Тригер може реагувати на такі події:
INSERT;
UPDATE;
DELETE;
TRUNCATE.
Події можна комбінувати:
AFTER INSERT OR UPDATE OR DELETE ON productsДля UPDATE можна обмежити тригер конкретними колонками:
AFTER UPDATE OF price, stock_quantity ON productsУ такому разі тригер запускатиметься лише тоді, коли команда UPDATE містить ці колонки.
У функції з RETURNS trigger доступні спеціальні змінні:
TG_OP — назва операції: INSERT, UPDATE, DELETE або TRUNCATE;
TG_TABLE_NAME — назва таблиці;
TG_TABLE_SCHEMA — схема таблиці;
TG_NAME — назва тригера;
NEW — новий рядок;
OLD — старий рядок.
NEW має сенс для INSERT та UPDATE, а OLD — для UPDATE та DELETE.
updated_at і аудитРозглянемо таблицю товарів. Для неї потрібно:
автоматично встановлювати час зміни;
записувати всі вставки, оновлення та видалення в журнал аудиту.
DROP TABLE IF EXISTS product_audit;
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL CHECK (price >= 0),
stock_quantity integer NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE product_audit (
audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id integer,
operation text NOT NULL,
old_data jsonb,
new_data jsonb,
changed_at timestamptz NOT NULL DEFAULT now()
);BEFORE UPDATE для поля updated_atФункція змінює значення NEW.updated_at до фактичного запису рядка в таблицю.
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at := now();
RETURN NEW;
END;
$$;
CREATE TRIGGER products_set_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();Коли виконується UPDATE, PostgreSQL спочатку формує нову версію рядка, потім викликає BEFORE-тригер. Функція змінює NEW, і вже цей змінений рядок записується в таблицю.
AFTER-тригер для аудитуДля аудиту потрібно зберегти старий і новий стан рядка у форматі jsonb.
CREATE OR REPLACE FUNCTION audit_product_changes()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO product_audit (
product_id,
operation,
old_data,
new_data
)
VALUES (
NEW.id,
TG_OP,
NULL,
to_jsonb(NEW)
);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO product_audit (
product_id,
operation,
old_data,
new_data
)
VALUES (
NEW.id,
TG_OP,
to_jsonb(OLD),
to_jsonb(NEW)
);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO product_audit (
product_id,
operation,
old_data,
new_data
)
VALUES (
OLD.id,
TG_OP,
to_jsonb(OLD),
NULL
);
RETURN OLD;
END IF;
RETURN NULL;
END;
$$;
CREATE TRIGGER products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION audit_product_changes();Тепер можна перевірити всі три операції:
INSERT INTO products (name, price, stock_quantity)
VALUES ('Mechanical keyboard', 2499.00, 15);
UPDATE products
SET price = 2299.00,
stock_quantity = 12
WHERE name = 'Mechanical keyboard';
DELETE FROM products
WHERE name = 'Mechanical keyboard';
SELECT
operation,
product_id,
old_data,
new_data,
changed_at
FROM product_audit
ORDER BY audit_id;Для INSERT у old_data буде NULL, для DELETE у new_data буде NULL, а для UPDATE будуть присутні обидва стани.
WHENТригер можна запускати лише за певної умови. Це допомагає не виконувати зайву логіку.
Наприклад, аудит оновлення ціни потрібен лише тоді, коли ціна справді змінилася:
CREATE OR REPLACE FUNCTION audit_price_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO product_audit (
product_id,
operation,
old_data,
new_data
)
VALUES (
NEW.id,
'PRICE_UPDATE',
jsonb_build_object('price', OLD.price),
jsonb_build_object('price', NEW.price)
);
RETURN NEW;
END;
$$;
CREATE TRIGGER products_price_audit
AFTER UPDATE OF price ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)
EXECUTE FUNCTION audit_price_change();IS DISTINCT FROM безпечно працює з NULL. На відміну від звичайного порівняння <>, воно правильно визначає відмінність навіть тоді, коли одне зі значень дорівнює NULL.
Важливо розрізняти:
UPDATE OF price перевіряє, чи вказана колонка price у команді UPDATE;
WHEN (OLD.price IS DISTINCT FROM NEW.price) перевіряє, чи фактично змінилося значення.
Наприклад, команда:
UPDATE products
SET price = price
WHERE id = 1;містить колонку price, але її значення не змінюється. У такому випадку умова WHEN не пропустить виконання тригера.
BEFORE-тригераОбмеження CHECK підходять для простих перевірок значень одного рядка. Тригер може реалізувати складнішу логіку, наприклад нормалізацію даних перед збереженням.
CREATE OR REPLACE FUNCTION normalize_product_name()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.name := regexp_replace(trim(NEW.name), '\s+', ' ', 'g');
IF NEW.name = '' THEN
RAISE EXCEPTION 'Назва товару не може бути порожньою';
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER products_normalize_name
BEFORE INSERT OR UPDATE OF name ON products
FOR EACH ROW
EXECUTE FUNCTION normalize_product_name();Тепер значення на кшталт:
Wireless mouseбуде збережено як:
Wireless mouseRAISE EXCEPTION перериває операцію. Якщо команда змінювала кілька рядків у межах однієї транзакції, помилка також призведе до помилки всієї SQL-команди.
Для складних правил, які залежать від інших рядків або таблиць, потрібно враховувати паралельні транзакції. Простий тригер не завжди достатній для забезпечення коректності в умовах конкуренції. Якщо правило можна виразити через PRIMARY KEY, UNIQUE, FOREIGN KEY або CHECK, краще використовувати відповідне обмеження.
DELETEТригери на видалення часто використовують для:
аудиту;
архівації;
очищення пов’язаних даних;
заборони видалення за певних умов.
Для DELETE нової версії рядка немає, тому використовується OLD:
CREATE OR REPLACE FUNCTION prevent_expensive_product_delete()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF OLD.price > 100000 THEN
RAISE EXCEPTION
'Не можна видалити дорогий товар з id=%',
OLD.id;
END IF;
RETURN OLD;
END;
$$;
CREATE TRIGGER products_prevent_expensive_delete
BEFORE DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION prevent_expensive_product_delete();Для BEFORE DELETE функція повинна повернути OLD, щоб дозволити видалення.
Якщо потрібно зафіксувати сам факт виконання операції, а не кожен рядок, використовується FOR EACH STATEMENT.
CREATE TABLE product_operation_log (
log_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
operation text NOT NULL,
executed_at timestamptz NOT NULL DEFAULT now()
);
CREATE OR REPLACE FUNCTION log_product_statement()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO product_operation_log (operation)
VALUES (TG_OP);
RETURN NULL;
END;
$$;
CREATE TRIGGER products_statement_log
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH STATEMENT
EXECUTE FUNCTION log_product_statement();Якщо виконати:
UPDATE products
SET stock_quantity = stock_quantity + 1;тригер вставить один запис у product_operation_log, навіть якщо було змінено багато товарів.
Тригери виконуються в межах тієї самої транзакції, що й операція, яка їх викликала.
Це означає:
якщо тригер вставив запис у журнал, цей запис буде частиною поточної транзакції;
якщо основна операція відкотиться, зміни тригера також відкотяться;
якщо тригер спричинить помилку, зазвичай буде скасовано всю поточну SQL-команду.
Наприклад, якщо AFTER INSERT-тригер не може вставити запис до таблиці аудиту через порушення обмеження, вставка товару також не завершиться успішно.
Тригери не є окремими фоновими процесами. Вони виконуються синхронно під час операції над даними.
Для однієї таблиці може існувати кілька тригерів з однаковим часом і подією.
У PostgreSQL тригери одного типу для однієї події виконуються в алфавітному порядку їхніх імен. Тому назви тригерів варто робити зрозумілими, а за потреби використовувати префікси:
products_01_normalize_name
products_02_validate_price
products_03_auditВодночас не слід будувати критично важливу логіку на неочевидному порядку тригерів. Краще об’єднати взаємозалежні дії в одну функцію або явно назвати тригери так, щоб порядок був зрозумілим.
Тригер можна тимчасово вимкнути або знову увімкнути:
ALTER TABLE products
DISABLE TRIGGER products_audit;
ALTER TABLE products
ENABLE TRIGGER products_audit;Тригер можна видалити:
DROP TRIGGER products_audit ON products;Функцію можна видалити лише після видалення тригерів, які її використовують:
DROP FUNCTION audit_product_changes();Зміна функції через CREATE OR REPLACE FUNCTION не вимагає повторного створення тригера.
NEW у тригері DELETEДля DELETE потрібно використовувати OLD:
-- Правильно для DELETE
RETURN OLD;NEW для видалення не містить нового рядка.
OLD у тригері INSERTДля INSERT попереднього рядка немає. Потрібно працювати з NEW.
RETURNФункція-тригер повинна повертати значення, навіть якщо воно не використовується в AFTER-тригері.
Для BEFORE-тригера повернення має значення:
RETURN NEW — дозволити вставку або оновлення;
RETURN OLD — дозволити видалення;
RETURN NULL — пропустити поточний рядок.
Тригер, який змінює ту саму таблицю, може повторно викликати себе:
-- Небезпечний шаблон
UPDATE products
SET updated_at = now()
WHERE id = NEW.id;У BEFORE-тригері зазвичай потрібно змінювати NEW, а не виконувати окремий UPDATE тієї самої таблиці.
Тригери приховують частину логіки від коду, який виконує INSERT, UPDATE або DELETE. Через це складну систему тригерів важче налагоджувати.
Тригер доречний, коли дія має бути гарантовано прив’язана до зміни даних незалежно від клієнта. Якщо логіка специфічна лише для одного сценарію застосунку, її інколи краще залишити на рівні цього застосунку.
Для простих правил варто надавати перевагу декларативним обмеженням:
NOT NULL;
CHECK;
UNIQUE;
PRIMARY KEY;
FOREIGN KEY.
Вони зрозуміліші для схеми та краще описують цілісність даних.
Тригер автоматично запускає функцію у відповідь на зміну даних.
Функція-тригер оголошується з RETURNS trigger.
BEFORE дає змогу змінити або скасувати операцію.
AFTER зручно використовувати для аудиту та побічних дій після успішної зміни.
FOR EACH ROW працює з NEW і OLD для кожного рядка.
FOR EACH STATEMENT виконується один раз для всієї SQL-команди.
TG_OP визначає поточну операцію.
WHEN дозволяє запускати тригер лише за потрібної умови.
Для INSERT використовується NEW, для DELETE — OLD.
Тригери виконуються в межах поточної транзакції.
Простим правилам цілісності краще надавати перевагу через обмеження таблиці, а не через тригери.