CREATE TRIGGER
CREATE TRIGGER — создать триггер
Синтаксис
CREATE [ OR REPLACE ] [ 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
создаёт новый триггер, а CREATE OR REPLACE TRIGGER
создаёт новый триггер или заменяет существующий. Триггер будет связан с указанной таблицей, представлением или сторонней таблицей и будет выполнять заданную функцию имя_функции
при определённых операциях с этой таблицей.
Чтобы заменить текущее определение существующего триггера, воспользуйтесь командой CREATE OR REPLACE 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 | Таблицы и сторонние таблицы | Таблицы, представления и сторонние таблицы |
TRUNCATE | — | Таблицы и сторонние таблицы | |
AFTER | INSERT /UPDATE /DELETE | Таблицы и сторонние таблицы | Таблицы, представления и сторонние таблицы |
TRUNCATE | — | Таблицы и сторонние таблицы | |
INSTEAD OF | INSERT /UPDATE /DELETE | Представления | — |
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
, окажется минимум один из перечисленных или если какой-нибудь из них будет генерируемым и при этом зависящим от столбца, который фигурирует в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
.
При создании триггера уровня строк для секционированной таблицы такой же «клонированный» триггер будет создан в каждой из её существующих секций; идентичный триггер будет установлен и в каждой секции, создаваемой или присоединяемой позднее. Если в дочерней таблице встретится триггер с конфликтующим именем, возникнет ошибка. Исключение составляет команда CREATE OR REPLACE TRIGGER
, которая заменит существующий одноимённый триггер клонированным. При отсоединения секции от родительской таблицы клонированные триггеры в ней удаляется.
Триггер для избранных столбцов (определённый с помощью UPDATE OF
) будет срабатывать, когда его столбцы перечислены в качестве целевых в списке имя_столбца
SET
команды UPDATE
. Изменения, вносимые в строки триггерами BEFORE UPDATE
, при этом не учитываются, поэтому значения столбцов можно изменить так, что триггер не сработает. И наоборот, при выполнении команды UPDATE ... SET x = x ...
триггер для столбца x
сработает, хотя значение столбца не меняется.
В триггере BEFORE
условие WHEN
вычисляется непосредственно перед возможным вызовом функции, поэтому проверка WHEN
существенно не отличается от проверки того же условия в начале функции триггера. В частности, учтите, что строка NEW
, которую видит ограничение, содержит текущие значения, возможно изменённые предыдущими триггерами. Кроме того, в триггере BEFORE
условие WHEN
не может проверять системные столбцы в строке NEW
(например, ctid
), так как они ещё не установлены.
В триггере 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
, то вместо исходного оператора, обращающегося к представлению, выполняются те операторы, что генерирует правило, поэтому вызываться будут триггеры, связанные с таблицами, к которым обращаются эти заменяющие операторы. Аналогично, для автоматически изменяемого представления выполнение операции сводится к переписыванию оператора в виде операции с базовой таблицей представления, так что срабатывать будут триггеры уровня операторов для базовой таблицы.
При изменении данных в секционированной таблице или таблице с потомками срабатывают триггеры уровня оператора, связанные с явно задействованной таблицей, но не триггеры уровня оператора для её секций или дочерних таблиц. Триггеры уровня строк, напротив, срабатывают для строк в затрагиваемых секциях или дочерних таблицах, даже если они явно не присутствуют в запросе. Если триггер уровня оператора был определён с переходными отношениями, названными в указании REFERENCING
, то в них будут видны образы строк из всех затронутых секций или дочерних таблиц. В случае с потомками в иерархии наследования образы строк будут содержать только столбцы, присутствующие в таблице, с которой связан триггер.
В настоящее время триггеры уровня строки с переходными отношениями нельзя определить для секций или дочерних таблиц в иерархии наследования. Кроме того, для секционированных таблиц нельзя определить триггеры INSTEAD OF
.
В настоящее время указание OR REPLACE
не поддерживается для триггеров ограничений.
Заменять существующий триггер в рамках транзакции, которая уже выполнила действие, изменяющее данные в таблице триггера, не рекомендуется. Решения по срабатыванию триггера, как и их составляющие, принятые ранее, не будут пересмотрены, поэтому эффект такой операции может быть неожиданным.
Некоторые общие задачи можно решить с применением встроенных триггерных функций, обойдясь без написания собственного кода; см. Раздел 9.29.
Примеры #
Выполнение функции check_account_update
перед любым изменением строк в таблице accounts
:
CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE FUNCTION check_account_update();
Изменение определения триггера, чтобы данная функция выполнялась только при указании столбца balance
в качестве целевого столбца команды UPDATE
:
CREATE OR REPLACE 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
в Postgres Pro реализует подмножество возможностей, описанных в стандарте SQL. В настоящее время в нём отсутствует следующая функциональность:
Тогда как имена переходных таблиц для триггеров
AFTER
задаются предложениемREFERENCING
стандартным образом, переменные строк, применяемые в триггерахFOR EACH ROW
нельзя объявлять в предложенииREFERENCING
. Порядок обращения к таким строкам зависит от языка, на котором написана триггерная функция, но для каждого языка он вполне определённый. Некоторые языки по сути действуют так, как будто в команде присутствует предложениеREFERENCING
с указаниемOLD ROW AS OLD NEW ROW AS NEW
.Стандарт позволяет использовать переходные таблицы с триггерами
UPDATE
, ограничивающими набор отслеживаемых столбцов, но тогда и набор строк, видимых в переходных таблицах, должен зависеть от списка целевых столбцов триггера. В настоящее время такое поведение в Postgres Pro не реализовано.Postgres Pro позволяет задать в качестве действия триггера только функцию, определённую пользователем. Стандарт допускает также выполнение ряда других команд SQL, например,
CREATE TABLE
. Однако это ограничение несложно преодолеть, создав пользовательскую функцию, выполняющую требуемые команды.
В стандарте SQL определено, что несколько триггеров должны срабатывать по порядку создания. Postgres Pro упорядочивает их по именам, так как это было признано более удобным.
В стандарте SQL определено, что триггеры BEFORE DELETE
при каскадном удалении срабатывают после завершения каскадного DELETE
. В Postgres Pro триггеры BEFORE DELETE
всегда срабатывают перед операцией удаления, даже если она каскадная. Это поведение выбрано как более логичное. Ещё одно отклонение от стандарта проявляется, когда триггеры BEFORE
, срабатывающие в результате ссылочной операции, изменяют строки или не дают выполнить изменение. Это может привести к нарушению ограничений или сохранению данных, не соблюдающих ссылочную целостность.
Возможность задать несколько действий для одного триггера с помощью ключевого слова OR
— реализованное в Postgres Pro расширение стандарта SQL.
Возможность вызывать триггеры для TRUNCATE
— реализованное в Postgres Pro расширение стандарта SQL, как и возможность определять триггеры на уровне оператора для представлений.
CREATE CONSTRAINT TRIGGER
— реализованное в Postgres Pro расширение стандарта SQL, так же как и указание OR REPLACE
.