Как написать триггер postgresql

Написать тригер в Postgres, который принимает id пользователя (user_id)

При создании нового пользователя в Postgres у меня стоит триггер который автоматически создает ему чат (по типу saved messages в telegram). У меня также в бд есть таблица chat_members, которая хранит участников чатов связью много ко многим пользователей и чатов. Необходимо написать триггер, который при создании любого чата будет автоматически записывать в таблицу chat_members его создателя, но я не знаю как передать id пользователя, так как в таблице chats нет такой колонки. Одним из решений является добавление колонки created_by в таблице chat, и запись туда создателя чата, а потом триггер берет этот id и создает запись в таблице chat_members. Моя схема бд в Supabase: Сам триггер и функция которая создает чат:

create function public.create_user_saved_chat() returns public.chats as $$ declare new_chat public.chats; begin insert into public.chats (type, name) values ('saved', 'Saved chat') returning * into new_chat; return new_chat; end; $$ language plpgsql security definer; 

create function public.handle_new_user() returns trigger as $$ begin perform public.create_user_saved_chat(); return new; end; $$ language plpgsql security definer; create trigger on_auth_user_created after insert on auth.users for each row execute procedure public.handle_new_user(); 

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. В документации есть таблица, которая поможет сориентироваться.

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

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

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

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

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

Просто и доступно про триггеры PostgreSQL

В свое время, когда только начинал разбираться с функциями, процедурами и триггерами в СУБД PostgreSQL столкнулся с тем что нормального, простого и не абстрактоного примера найти было невозможно.

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

Так как задача простая, то я не буду приводить рабочие таблицы со всеми полями. Для примера возьмем максимально простые (расширить всегда можно, главное смысл понять):

-- Таблица пользователей CREATE TABLE users ( "name" text ) -- Таблица логов CREATE TABLE logs ( "text" text, "added" timestamp without time zone )

Все максимально просто. Что теперь надо сделать для добавления логов ?

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

В нашем случае мы будем обрабатывать операции Update, Insert и Delete для базы users.

Сам триггер описывается на PL/pgSQL как:

CREATE TRIGGER t_user AFTER INSERT OR UPDATE OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE add_to_log ();

Собственно тут все понятно. Создать триггер t_user который будет выполняться AFTER (после) команд INSERT, UPDATE, DELETE для каждой строки вызывать функцию add_to_log();

Тут есть небольшая особенность. Если попытаться выполнить эту инструкцию то получим ошибку, что функции add_to_log() нету. Значит пред тем как создавать триггер нужно создать функцию add_to_log().

Собственно вот она:

CREATE OR REPLACE FUNCTION add_to_log() RETURNS TRIGGER AS $$ DECLARE mstr varchar(30); astr varchar(100); retstr varchar(254); BEGIN IF TG_OP = 'INSERT' THEN astr = NEW.name; mstr := 'Add new user '; retstr := mstr || astr; INSERT INTO logs(text,added) values (retstr,NOW()); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN astr = NEW.name; mstr := 'Update user '; retstr := mstr || astr; INSERT INTO logs(text,added) values (retstr,NOW()); RETURN NEW; ELSIF TG_OP = 'DELETE' THEN astr = OLD.name; mstr := 'Remove user '; retstr := mstr || astr; INSERT INTO logs(text,added) values (retstr,NOW()); RETURN OLD; END IF; END; $$ LANGUAGE plpgsql;

Что тут происходит ? Собственно определяется новая функция, без входящих параметров, возвращает специальный тип TRIGGER. Для внутреннего использования определяем 3-и переменные в разделе DECLARE. В самой процедуре смотрим переменную TG_OP (внутренняя переменная триггера которая определяет с какой операцией была вызвана процедура). В зависимости от операции определяем переменную mstr, собираем строку retstr которая будет записана в базу данных (обратите внимание как производится контаминация строк в pgsql, через ||) и собственно делаем запись в таблицу логов (INSERT INTO). Надо пояснить что такое переменные NEW и OLD. Это собственно строки которые обрабатывает триггер. В случае INSERT переменная NEW будет содержать новую строку, а OLD будет пустая, в случае UPDATE обе переменные будут определены (соответствующими данными), а в случае DELETE переменная NEW будет пустая, OLD содержать удаляемую строку.

Если все правильно сделали то теперь при любой работе с таблицей users без нашего участия будет записываться лог действий с таблицей. Все очень прозрачно, ничего вызывать стороннего не надо.

У данного метода есть 2-е очевидные плюшки по сравнению с «программным методом» когда программист в бизнес логике приложения определяет точки вызова записи в лог:

• скорость работы, особенно это будет заметно на средне и высоко нагруженных приложениях,
• абсолютная абстракция от языка приложения. Администратору СУБД плевать с большой колокольни на то, какую там Яву или питон использовал программист приложения. Вносить изменения в логику работы базы без вмешательства в код приложения.

Естественно что это очень простой пример, и триггеры используются не только для логов 🙂

7. Создание триггеров в PostgreSql

Триггер определяет операцию, которая должна выполняться при наступлении некоторого события в базе данных. Триггеры срабатывают при выполнении с таблицей команды SQL INSERT , UPDATE или DELETE .
В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер.

Синтаксис определения триггера

CREATE TRIGGER триггер
BEFORE | AFTER > < событие [ OR событие ] > ON таблица
FOR EACH < ROW | STATEMENT >
EXECUTE PROCEDURE функция ( аргументы )

Ниже приводятся краткие описания компонентов этого определения.

• CREATE TRIGGER триггер. В аргументе триггер указывается произвольное имя создаваемого триггера. Имя может совпадать с именем триггера, уже существу ющего в базе данных при условии, что этот триггер установлен для другой таблицы. Кроме того, по аналогии с большинством других несистемных объек тов баз данных, имя триггера (в сочетании с таблицей, для которой он устанав ливается) должно быть уникальным лишь в контексте базы данных, в которой он создается
• < BEFORE | AFTER >. Ключевое слово BEFORE означает, что функция должна выпол няться перед попыткой выполнения операции, включая все встроенные про верки ограничений данных, реализуемые при выполнении команд INSERT и DELETE . Ключевое слово AFTER означает, что функция вызывается после завершения операции, приводящей в действие триггер.
• < событие [ OR событие . ] >. События SQL , поддерживаемые в PostgreSQL : INSERT , UPDATE или DELETE . При перечислении нескольких событий в качестве разделителя используется ключевое слово OR .
• ON таблица. Имя таблицы, модификация которой заданным событием приво дит к срабатыванию триггера.
• FOR EACH < ROW | STATEMENT >. Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Ключевое слово ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT .
• EXECUTE PROCEDURE функция ( аргументы ). Имя вызываемой функции с аргу ментами. На практике аргументы при вызове триггерных функций не используются.

Синтаксис определения триггерной функции

CREATE FUNCTION функция () RETURNS trigger AS ‘
DECLARE
объявления ;
BEGIN
команды ;
END ; ‘
LANGUAGE plpgsql ;

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

Имя
Тип
Описание

К отдельным полям записей NEW и OLD в триггерных процедурах обращаются следующим образом: NEW.names , OLD.rg.

Примеры создания триггеров

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

— Создание триггерной функции
CREATE FUNCTION trigger_s_before_del () RETURNS trigger AS ‘
BEGIN
if (select count(*) from spj a where trim(a.ns)=trim(OLD.ns))>0
then delete from spj where trim(spj.ns)=trim(OLD.ns);
end if;
return OLD;
END;
‘ LANGUAGE plpgsql;

— Создание триггера
CREATE TRIGGER tr_s_del_befor
BEFORE DELETE ON s FOR EACH ROW
EXECUTE PROCEDURE trigger_s_before_del();

—Проверка работы триггера
Delete from s where ns=’S2’;

Пример 2. Создание триггера-генератора для таблицы поставщиков s.

Триггер выполняется перед вставкой новой записи в таблицу поставщиков s. Триггер проверяет значения, которые должна содержать новая запись (record NEW) и может их изменить:

• eсли не указан номер поставщика – он генерируется по схеме – S+ уникальный номер из последовательности;
• eсли не указано имя поставщика – оно генерируется по схеме – Postawchik_ + уникальный номер из последовательности;
• eсли не указан город – ставится значение по умолчанию – “Novosibirsk” ;
• если не указан рейтинг или рейтинг

— Создание последовательности
CREATE SEQUENCE s_seq INCREMENT BY 1 START WITH 25;

— Создание триггерной функции
— в этой функции вызывается перегружаемая функция nvl, ее определение здесь
CREATE FUNCTION trigger_s_before_lns () RETURNS trigger AS ‘
BEGIN
NEW.ns=nvl(NEW.ns,’S’||trim(to_char(nextval(‘s_seq’),’99999′)));
NEW.names=nvl(NEW.names,’Postawchik_’||trim(to_char(currval(‘s_seq’),’99999′)));
NEW.town = nvl(NEW. town, ‘Novosibirsk’ );
if (nvl(NEW.rg,0) <=0) then
If NEW.town= ‘Novosibirsk’ then NEW.rg=10;
else NEW.rg=0;
end if;
end if;
return NEW;
END;
‘ LANGUAGE plpgsql;

— Создание триггера
CREATE TRIGGER s_bi
BEFORE INSERT ON s FOR EACH ROW
EXECUTE PROCEDURE trigger_s_before_lns ()

—Проверка работы триггера
insert into s values(null,null,null,null);
insert into s values(null,null,null,null);
insert into s values(null,null,null,null);
insert into s values(null,’Ivanov’,null,null);
insert into s values(null,’Sidorov’,50,null);
insert into s values(null,’Petrov’,null,’Moskva’);