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

SQL-Ex blog

Триггер представляет собой блок кода, который автоматически выполняется после исполнения некоторой операции с таблицей или представлением базы данных, точнее, после операции Insert, Update, Delete. Например, в приложении банка триггер может использоваться для вставки данных в таблицу истории/аудита для всех имеющих место исходных транзакций.

Некоторые ключевые моменты:

Триггер может вызываться до или после события. Триггеры весьма полезны, когда база данных используется множеством приложений, и важно постоянно сохранять базу данных в согласованном состоянии, когда бы ни изменялись определенные данные.
Хотя триггеры очень полезны для автоматизации изменений данных и позволяют легко вести аудит, у триггеров имеются также и недостатки. Поскольку триггеры выполняются при всякой модификации данных, это может привести к избыточной нагрузке на систему.
Еще одним недостатком триггеров является сложность отслеживания и понимания их логики.

Триггер уровня строки

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

Триггер уровня оператора

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

Триггеры в PostgreSQL

Триггеры в PostgreSQL поддерживают операции truncate.
PostgreSQL не поддерживает триггеры, не имеющие связанной триггерной функции.

Create trigger — используется для создания триггера
Drop trigger — используется для удаления триггера
Alter trigger — используется для изменения имени существующего триггера
Disable trigger — используется для отключения заданного или всех триггеров, связанных с таблицей
Enable trigger — используется для включения конкретного или всех триггеров, связанных с таблицей

Базовый синтаксис триггера в PostgreSQL:

CREATE TRIGGER trigger_name
< событие >
ON table_name
[FOR [EACH] < ROW | STATEMENT >]
EXECUTE PROCEDURE trigger_function

Давайте рассмотрим этот синтаксис.

Для начала триггеру дается имя — trigger_name. За ним следует время срабатывания триггера, которым может быть либо before (до), либо after (после), в зависимости от операции, которая должна быть выполнена на целевой таблице. Затем указывается одно из следующих событий — insert, update, delete, truncate. Затем указывается имя таблицы с последующим типом триггера, которым является row (на строку) или statement (на оператор). Наконец, указывается связанная с триггером функция — trigger_function:

Базовый синтаксис триггерной функции PostgreSQL

CREATE FUNCTION trigger_function() 
RETURNS TRIGGER 
LANGUAGE PLPGSQL 
AS $$ 
BEGIN 
-- логика триггера 
END; 
$$

Триггерная функция не имеет аргументов, а возвращаемое значение имеет тип trigger. После определения триггерной функции она должна быть связана с одним или более событий, вызывающих срабатывание триггера, таких как insert, update и delete.

Пример триггера уровня строки

Сценарий: Имеется две таблицы stocks и stock_audits; для каждой строки данных, вставленных в таблицу stocks выполняется триггер stocks_trigger и вставляет одну строку данных в другую таблицу stock_audits.

CREATE TRIGGER stocks_trigger 
AFTER INSERT ON public."Stocks" 
FOR EACH ROW 
EXECUTE PROCEDURE stock_auditfunc();

CREATE OR REPLACE FUNCTION stock_auditfunc() RETURNS TRIGGER AS $my_table$ 
BEGIN 
INSERT INTO stocks_audit(stock_id, entry_date) VALUES (new.ID, current_timestamp); 
RETURN NEW; 
END; 
$my_table$ LANGUAGE plpgsql;

Таблицы до вставки:

Таблица после вставки и выполнения триггера:

INSERT INTO public."Stocks" 
VALUES (1,3000,'TCS');

Замечание: Для массовой операции, например, когда сразу обрабатывается 100 строк, триггер уровня строки выполняется 100 раз.

Триггер уровня оператора

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

CREATE TRIGGER stocks_trigger 
AFTER INSERT ON public."Stocks" 
FOR EACH STATEMENT 
EXECUTE PROCEDURE stock_auditfunc();

Доступ к триггерам PostgreSQL с помощью PgAdmin и консольного приложения PSQL

С триггерами в PostgreSQL можно эффективно работать либо с помощью PgAdmin, либо посредством psql-терминала, как показывается ниже.

Доступ к триггерам посредством PgAdmin:

Триггеры имеют отношение к таблицам и перечисляются в ветке каждой таблицы, как показано ниже,
Servers->User Server->Databases->User Database->Schemas->User Schema->Tables-> User Tables->Triggers

Доступ к триггерам посредством psql:

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

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’);

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

CREATE TRIGGER — создать триггер

Синтаксис

CREATE [ CONSTRAINT ] TRIGGER имя < BEFORE | AFTER | INSTEAD OF >< событие [ OR . ] > ON имя_таблицы [ FROM ссылающаяся_таблица ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ REFERENCING < < OLD | NEW >TABLE [ AS ] имя_переходного_отношения > [ . ] ] [ FOR [ EACH ] < ROW | STATEMENT >] [ WHEN ( условие ) ] EXECUTE < FUNCTION | PROCEDURE >имя_функции ( аргументы ) Здесь допускается событие: INSERT UPDATE [ OF имя_столбца [, . ] ] DELETE TRUNCATE

Описание

CREATE TRIGGER создаёт новый триггер. Триггер будет связан с указанной таблицей, представлением или сторонней таблицей и будет выполнять заданную функцию имя_функции при определённых операциях с этой таблицей.

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

Триггер с пометкой FOR EACH ROW вызывается один раз для каждой строки, изменяемой в процессе операции. Например, операция DELETE , удаляющая 10 строк, приведёт к срабатыванию всех триггеров ON DELETE в целевом отношении 10 раз подряд, по одному разу для каждой удаляемой строки. Триггер с пометкой FOR EACH STATEMENT , напротив, вызывается только один раз для конкретной операции, вне зависимости от того, как много строк она изменила (в частности, при выполнении операции, изменяющей ноль строк, всё равно будут вызваны все триггеры FOR EACH STATEMENT ).

Триггеры, срабатывающие в режиме INSTEAD OF , должны быть помечены FOR EACH ROW и могут быть определены только для представлений. Триггеры BEFORE и AFTER для представлений должны быть помечены FOR EACH STATEMENT .

Кроме того, триггеры можно определить и для команды TRUNCATE , но только типа FOR EACH STATEMENT .

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

Когда	Событие	На уровне строк	На уровне оператора
BEFORE	INSERT / UPDATE / DELETE	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
BEFORE	TRUNCATE	—	Таблицы
AFTER	INSERT / UPDATE / DELETE	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
AFTER	TRUNCATE	—	Таблицы
INSTEAD OF	INSERT / UPDATE / DELETE	Представления	—
INSTEAD OF	TRUNCATE	—	—

Кроме того, в определении триггера можно указать логическое условие WHEN , которое определит, вызывать триггер или нет. В триггерах на уровне строк условия WHEN могут проверять старые и/или новые значения столбцов в строке. Триггеры на уровне оператора так же могут содержать условие WHEN , хотя для них это не столь полезно, так как в этом условии нельзя ссылаться на какие-либо значения в таблице.

Если для одного события определено несколько триггеров одного типа, они будут срабатывать в алфавитном порядке их имён.

Когда указывается параметр CONSTRAINT , эта команда создаёт триггер ограничения. Он подобен обычным триггерам, но отличается тем, что время его срабатывания можно изменить командой SET CONSTRAINTS . Триггеры ограничений должны быть триггерами типа AFTER ROW для обычных (не сторонних) таблиц. Они могут срабатывать либо в конце оператора, вызвавшего целевое событие, либо в конце содержащей его транзакции; в последнем случае они называются отложенными. Срабатывание ожидающего отложенного триггера можно вызвать немедленно, воспользовавшись командой SET CONSTRAINTS . Предполагается, что триггеры ограничений будут генерировать исключения при нарушении ограничений.

Когда указывается REFERENCING , для триггера собираются переходные отношения, представляющие собой множества строк, включающие все строки, которые были добавлены, удалены или изменены текущим оператором SQL. Это позволяет триггеру наблюдать общую картину того, что сделал оператор, а не только одну строку за другой. Это указание допускается только для триггера AFTER , не являющегося триггером ограничения; кроме того, если это триггер для UPDATE , у него должен отсутствовать список имён_столбцов . Указание OLD TABLE может быть задано только один раз и только для триггера, который может срабатывать при UPDATE или DELETE ; оно создаёт переходное отношение, содержащее образы-до-изменения всех строк, модифицированных или удалённых оператором. Указание NEW TABLE , подобным образом, может быть задано только единожды и только для триггера, который может срабатывать для UPDATE или INSERT ; оно создаёт переходное отношение, содержащее образы-после-изменения всех строк, модифицированных или добавленных оператором.

SELECT не изменяет никакие строки, поэтому создавать триггеры для SELECT нельзя. Для решения задач, в которых требуются подобные триггеры, могут подойти правила или представления.

За дополнительными сведениями о триггерах обратитесь к Главе 39.

Параметры

Имя, назначаемое новому триггеру. Это имя должно отличаться от имени любого другого триггера в этой же таблице. Имя не может быть дополнено схемой — триггер наследует схему от своей таблицы. Для триггеров ограничений это имя также используется, когда требуется скорректировать поведение триггера с помощью команды SET CONSTRAINTS . BEFORE
AFTER
INSTEAD OF

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

Принимает одно из значений: INSERT , UPDATE , DELETE или TRUNCATE ; этот параметр определяет событие, при котором будет срабатывать триггер. Несколько событий можно указать, добавив между ними слово OR , если только не запрашиваются переходные отношения.

Для событий UPDATE можно указать список столбцов, используя такую запись:

UPDATE OF имя_столбца1 [, имя_столбца2 . ]

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

Для событий INSTEAD OF UPDATE указание списка столбцов не допускается. Список столбцов также нельзя задать, когда запрашиваются переходные отношения. имя_таблицы

Имя (возможно, дополненное схемой) таблицы, представления или сторонней таблицы, для которых предназначен триггер. ссылающаяся_таблица

Имя (возможно, дополненное схемой) другой таблицы, на которую ссылается ограничение. Оно используется для ограничений внешнего ключа и не рекомендуется для обычного применения. Это указание допускается только для триггеров ограничений. DEFERRABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
INITIALLY DEFERRED

Время срабатывания триггера по умолчанию. Подробнее возможные варианты описаны в документации CREATE TABLE . Это указание допускается только для триггеров ограничений. REFERENCING

Это ключевое слово непосредственно предшествует объявлению одного или двух имён, по которым можно будет обращаться к переходным отношениями, образуемым при выполнении целевого оператора. OLD TABLE
NEW TABLE

Это предложение указывает, будет ли следующее имя относиться к переходному отношению с образом-до-изменения или к переходному отношению с образом-после-изменения. имя_переходного_отношения

Имя (неполное, без схемы), которое будет использоваться в триггере для обращения к этому переходному отношению. FOR EACH ROW
FOR EACH STATEMENT

Определяет, будет ли функция триггера срабатывать один раз для каждой строки, либо для SQL-оператора. Если не указано ничего, подразумевается FOR EACH STATEMENT (для оператора). Для триггеров ограничений можно указать только FOR EACH ROW . условие

Логическое выражение, определяющее, будет ли выполняться функция триггера. Если для триггера задано указание WHEN , функция будет вызываться, только когда условие возвращает true . В триггерах FOR EACH ROW условие WHEN может ссылаться на значения столбца в старой и/или новой строке, в виде OLD. имя_столбца и NEW. имя_столбца , соответственно. Разумеется, триггеры INSERT не могут ссылаться на OLD , а триггеры DELETE не могут ссылаться на NEW .

Триггеры INSTEAD OF не поддерживают условия WHEN .

В настоящее время выражения WHEN не могут содержать подзапросы.

Учтите, что для триггеров ограничений вычисление условия WHEN не откладывается, а выполняется немедленно после операции, изменяющей строки. Если результат условия — ложь, сам триггер не откладывается для последующего выполнения. имя_функции

Заданная пользователем функция, объявленная как функция без аргументов и возвращающая тип trigger , которая будет вызываться при срабатывании триггера.

В синтаксисе CREATE TRIGGER ключевые слова FUNCTION и PROCEDURE равнозначны, но указываемая триггерная функция должна в любом случае быть функцией, а не процедурой. Ключевое слово PROCEDURE здесь поддерживается по историческим причинам и считается устаревшим. аргументы

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

Замечания

Чтобы создать триггер, пользователь должен иметь право TRIGGER для этой таблицы. Также пользователь должен иметь право EXECUTE для триггерной функции.

Для удаления триггера применяется команда DROP TRIGGER .

Триггер для избранных столбцов (определённый с помощью UPDATE OF имя_столбца ) будет срабатывать, когда его столбцы перечислены в качестве целевых в списке SET команды UPDATE . Изменения, вносимые в строки триггерами BEFORE UPDATE , при этом не учитываются, поэтому значения столбцов можно изменить так, что триггер не сработает. И наоборот, при выполнении команды UPDATE . SET x = x . триггер для столбца x сработает, хотя значение столбца не меняется.

Некоторые общие задачи можно решить с применением встроенных триггерных функций, обойдясь без написания собственного кода; см. Раздел 9.27.

В триггере BEFORE условие WHEN вычисляется непосредственно перед возможным вызовом функции, поэтому проверка WHEN существенно не отличается от проверки того же условия в начале функции триггера. В частности, учтите, что строка NEW , которую видит ограничение, содержит текущие значения, возможно изменённые предыдущими триггерами. Кроме того, в триггере BEFORE условие WHEN не может проверять системные столбцы в строке NEW (например, oid ), так как они ещё не установлены.

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

В некоторых случаях одна команда SQL может вызывать сразу нескольких видов триггеров. Например, INSERT с предложением ON CONFLICT DO UPDATE может выполнять операции как добавления, так и изменения, так что она при необходимости будет вызывать триггеры обоих видов. При этом переходные отношения, предоставляемые триггерам, будут разными в зависимости от типа события; то есть триггер INSERT будет видеть только добавленные строки, а триггер UPDATE — только изменённые.

Изменения или удаления строк, вызванные действиями по обеспечению целостности внешнего ключа, например, ON UPDATE CASCADE или ON DELETE SET NULL , считаются частью SQL-команды, вызвавшей эти действия (заметьте, что такие действия не могут быть отложенными). В затрагиваемой таблице будут вызваны соответствующие триггеры, и таким образом появляется возможность вызова триггеров для SQL-команды, не соответствующей непосредственно их типу. В простых ситуациях триггеры, запрашивающие переходные отношения, будут видеть все изменения, произведённые в их таблице одной исходной командой SQL, в виде одного переходного отношения. Однако возможны случаи, в которых присутствие триггера AFTER ROW , запрашивающего переходные отношения, приведёт к тому, что операции для обеспечения целостности внешнего ключа, вызванные одной SQL-командой, будут разделены на несколько этапов, и на каждом будут свои переходные отношения. В таких случаях все существующие триггеры уровня оператора будут срабатывать единожды при создании переходного отношения, что гарантирует, что эти триггеры будут видеть каждую обрабатываемую строку в переходном отношении один и только один раз.

Триггеры уровня операторов для представления срабатывают, только если операция с представлением обрабатывается триггером уровня строк INSTEAD OF . Если операция обрабатывается правилом INSTEAD , то вместо исходного оператора, обращающегося к представлению, выполняются те операторы, что генерирует правило, поэтому вызываться будут триггеры, связанные с таблицами, к которым обращаются эти заменяющие операторы. Аналогично, для автоматически изменяемого представления выполнение операции сводится к переписыванию оператора в виде операции с базовой таблицей представления, так что срабатывать будут триггеры уровня операторов для базовой таблицы.

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

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

В PostgreSQL до версии 7.3 обязательно требовалось объявлять триггерные функции, как возвращающие фиктивный тип opaque , а не trigger . Для поддержки загрузки старых файлов экспорта БД, команда CREATE TRIGGER принимает функции с объявленным типом результата opaque , но при этом выдаётся предупреждение и тип результата меняется на trigger .

Примеры

Выполнение функции check_account_update перед любым изменением строк в таблице accounts :

CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update();

То же самое, но функция триггера будет выполняться, только если столбец balance присутствует в списке целевых столбцов команды UPDATE :

CREATE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update();

В этом примере функция будет выполняться, если значение столбца balance в действительности изменилось:

CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) 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();

Выполнение для каждой строки функции view_insert_row , которая будет вставлять строки в нижележащие таблицы представления:

CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view FOR EACH ROW EXECUTE FUNCTION view_insert_row();

Выполнение функции check_transfer_balances_to_zero для каждого оператора, проверяющей, что строки transfer в совокупности дают нулевой баланс:

CREATE TRIGGER transfer_insert AFTER INSERT ON transfer REFERENCING NEW TABLE AS inserted FOR EACH STATEMENT EXECUTE FUNCTION check_transfer_balances_to_zero();

Выполнение функции check_matching_pairs для каждой строки, проверяющей, что соответствующие пары пунктов изменены синхронно (одним оператором):

CREATE TRIGGER paired_items_update AFTER UPDATE ON paired_items REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH ROW EXECUTE FUNCTION check_matching_pairs();

В Разделе 39.4 приведён полный пример функции триггера, написанной на C.

Совместимость

Оператор CREATE TRIGGER в PostgreSQL реализует подмножество возможностей, описанных в стандарте SQL . В настоящее время в нём отсутствует следующая функциональность:

Тогда как имена переходных таблиц для триггеров AFTER задаются предложением REFERENCING стандартным образом, переменные строк, применяемые в триггерах FOR EACH ROW нельзя объявлять в предложении REFERENCING . Порядок обращения к таким строкам зависит от языка, на котором написана триггерная функция, но для каждого языка он вполне определённый. Некоторые языки по сути действуют так, как будто в команде присутствует предложение REFERENCING с указанием OLD ROW AS OLD NEW ROW AS NEW .

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

В стандарте SQL определено, что несколько триггеров должны срабатывать по порядку создания. PostgreSQL упорядочивает их по именам, так как это было признано более удобным.

В стандарте SQL определено, что триггеры BEFORE DELETE при каскадном удалении срабатывают после завершения каскадного DELETE . В PostgreSQL триггеры BEFORE DELETE всегда срабатывают перед операцией удаления, даже если она каскадная. Это поведение выбрано как более логичное. Ещё одно отклонение от стандарта проявляется, когда триггеры BEFORE , срабатывающие в результате ссылочной операции, изменяют строки или не дают выполнить изменение. Это может привести к нарушению ограничений или сохранению данных, не соблюдающих ссылочную целостность.

Возможность задать несколько действий для одного триггера с помощью ключевого слова OR — реализованное в PostgreSQL расширение стандарта SQL.

Возможность вызывать триггеры для TRUNCATE — реализованное в PostgreSQL расширение стандарта SQL, как и возможность определять триггеры на уровне оператора для представлений.

CREATE CONSTRAINT TRIGGER — реализованное в PostgreSQL расширение стандарта SQL .

См. также

Пред.	Наверх	След.
CREATE TRANSFORM	Начало	CREATE TYPE

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

CREATE TRIGGER — создать триггер

Синтаксис

CREATE [ CONSTRAINT ] TRIGGER имя < BEFORE | AFTER | INSTEAD OF >< событие [ OR . ] > ON имя_таблицы [ FROM ссылающаяся_таблица ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ FOR [ EACH ] < ROW | STATEMENT >] [ WHEN ( условие ) ] EXECUTE PROCEDURE имя_функции ( аргументы ) Здесь допускается событие: INSERT UPDATE [ OF имя_столбца [, . ] ] DELETE TRUNCATE

Описание

Кроме того, триггеры можно определить и для команды TRUNCATE , но только типа FOR EACH STATEMENT .

Когда	Событие	На уровне строк	На уровне оператора
BEFORE	INSERT / UPDATE / DELETE	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
BEFORE	TRUNCATE	—	Таблицы
AFTER	INSERT / UPDATE / DELETE	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
AFTER	TRUNCATE	—	Таблицы
INSTEAD OF	INSERT / UPDATE / DELETE	Представления	—
INSTEAD OF	TRUNCATE	—	—

Когда указывается параметр CONSTRAINT , эта команда создаёт триггер ограничения. Он подобен обычным триггерам, но отличается тем, что время его срабатывания можно изменить командой SET CONSTRAINTS . Триггеры ограничений должны работать в режиме AFTER ROW . Они могут срабатывать либо в конце оператора, вызвавшего целевое событие, либо в конце содержащей его транзакции; в последнем случае они называются отложенными. Срабатывание ожидающего отложенного триггера можно вызвать немедленно, воспользовавшись командой SET CONSTRAINTS . Предполагается, что триггеры ограничений будут генерировать исключения при нарушении ограничений.

SELECT не изменяет никакие строки, поэтому создавать триггеры для SELECT нельзя. В случае подобной потребности будут более уместны правила и представления.

За дополнительными сведениями о триггерах обратитесь к Главе 37.

Параметры

Для событий UPDATE можно указать список столбцов, используя такую запись:

UPDATE OF имя_столбца1 [, имя_столбца2 . ]

Для событий INSTEAD OF UPDATE список столбцов задать нельзя. имя_таблицы

Время срабатывания триггера по умолчанию. Подробнее возможные варианты описаны в документации CREATE TABLE . Это указание допускается только для триггеров ограничений. FOR EACH ROW
FOR EACH STATEMENT

Определяет, будет ли процедура триггера срабатывать один раз для каждой строки, либо для SQL-оператора. Если не указано ничего, подразумевается FOR EACH STATEMENT (для оператора). Для триггеров ограничений можно указать только FOR EACH ROW . условие

Триггеры INSTEAD OF не поддерживают условия WHEN .

В настоящее время выражения WHEN не могут содержать подзапросы.

Заданная пользователем функция, объявленная как функция без аргументов и возвращающая тип trigger , которая будет вызываться при срабатывании триггера. аргументы

Замечания

Для удаления триггера применяется команда DROP TRIGGER .

Примеры

Выполнение функции check_account_update перед любым изменением строк в таблице accounts :

CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE PROCEDURE check_account_update();

CREATE TRIGGER check_update BEFORE UPDATE OF balance ON accounts FOR EACH ROW EXECUTE PROCEDURE check_account_update();

В этом примере функция будет выполняться, если значение столбца balance в действительности изменилось:

CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE PROCEDURE check_account_update();

Вызов функции, ведущей журнал изменений в accounts , но только если что-то изменилось:

CREATE TRIGGER log_update AFTER UPDATE ON accounts FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) EXECUTE PROCEDURE log_account_update();

CREATE TRIGGER view_insert INSTEAD OF INSERT ON my_view FOR EACH ROW EXECUTE PROCEDURE view_insert_row();

В Разделе 37.4 приведён полный пример функции триггера, написанной на C.

Совместимость

SQL позволяет определить синонимы для строк « old » и « new » или таблиц, которые затем можно будет использовать в определении действия триггера (например, CREATE TRIGGER . ON имя_таблицы REFERENCING OLD ROW AS некоторое_имя NEW ROW AS другое_имя. ). PostgreSQL позволяет писать процедуры триггеров на различных языках, так что механизм доступа к данным зависит от конкретного языка.

PostgreSQL не позволяет обращаться к старой и новой таблице в триггерах на уровне оператора, т. е. к таблицам, содержащим все старые и/или новые строки, обозначаемые как OLD TABLE и NEW TABLE в стандарте SQL .

CREATE CONSTRAINT TRIGGER — реализованное в PostgreSQL расширение стандарта SQL .

См. также

Пред.	Наверх	След.
CREATE TRANSFORM	Начало	CREATE TYPE

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

SQL-Ex blog

Некоторые ключевые моменты:

Триггер уровня строки

Триггер уровня оператора

Триггеры в PostgreSQL

Базовый синтаксис триггера в PostgreSQL:

Базовый синтаксис триггерной функции PostgreSQL

Пример триггера уровня строки

Триггер уровня оператора

Доступ к триггерам PostgreSQL с помощью PgAdmin и консольного приложения PSQL

Доступ к триггерам посредством PgAdmin:

Доступ к триггерам посредством psql:

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

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

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

Синтаксис

Описание

Параметры

Замечания

Примеры

Совместимость

См. также

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

Синтаксис

Описание

Параметры

Замечания

Примеры

Совместимость

См. также

Добавить комментарий Отменить ответ