CREATE TRIGGER

примечание

Эта страница переведена при помощи нейросети GigaChat.

CREATE TRIGGER - создание нового триггера.

Синтаксис

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 )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

Описание

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

Чтобы заменить текущее определение существующего триггера, используйте CREATE OR REPLACE TRIGGER, указав имя существующего триггера и родительскую таблицу. Все остальные свойства будут заменены.

Триггер можно настроить на выполнение:

до выполнения операции (до проверки ограничений и попытки выполнения INSERT, UPDATE или DELETE);
после выполнения операции (после проверки ограничений и выполнения INSERT, UPDATE или DELETE);
вместо выполнения операции — используется только для представлений при вставке, обновлении или удалении.

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

Триггер с пометкой FOR EACH ROW вызывается для каждой строки, измененной операцией. Например, DELETE, затрагивающий 10 строк, вызовет такой триггер 10 раз — по одному разу на строку. Триггер с пометкой 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, но оно бесполезно, так как не имеет доступа к данным таблицы.

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

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

Параметр REFERENCING позволяет собирать переходные отношения — наборы строк, вставленных, удаленных или измененных текущим SQL-оператором. Это дает триггеру «глобальное» представление о выполненной операции, а не только по одной строке. Этот параметр разрешен только для триггеров AFTER, которые не являются триггерами ограничения. Если триггер обрабатывает UPDATE, то нельзя указывать список столбцов. OLD TABLE можно указать только один раз, только для UPDATE или DELETE. Она создает таблицу со «старыми» значениями затронутых строк. NEW TABLE также указывается один раз, только для UPDATE или INSERT, и содержит «новые» значения строк после операции.

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

Подробнее о триггерах смотрите в разделе «Триггеры» документации.

Параметры

name: Задает имя, которое будет присвоено новому триггеру. Оно должно отличаться от имени любого другого триггера для той же таблицы. Имя не может быть указано с квалификацией схемы — триггер наследует схему своей таблицы. Для триггера ограничения это также имя, которое используется при изменении поведения триггера с помощью команды SET CONSTRAINTS.

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

event: Указывает, какое событие вызывает срабатывание триггера. Одно из событий: INSERT, UPDATE, DELETE или TRUNCATE. Можно указать несколько событий с помощью OR, кроме случаев, когда используются переходные отношения.
Для событий UPDATE можно указать список столбцов в следующем формате:

UPDATE OF column_name1 [, column_name2 ... ]

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

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

table_name: Задает имя таблицы, представления или внешней таблицы, при необходимости дополненное схемой, для которой создается триггер.

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

DEFERRABLE NOT DEFERRABLE INITIALLY IMMEDIATE INITIALLY DEFERRED: Устанавливает поведение времени выполнения триггера по умолчанию. Смотрите документацию к CREATE TABLE для подробностей. Эти параметры доступны только для триггеров ограничений.

REFERENCING: Является ключевым словом, предшествующим объявлению одного или двух имен, по которым можно будет обращаться к переходным отношениями, образуемым при выполнении целевого оператора.

OLD TABLE NEW TABLE: Указывает, является ли следующее имя отношением «до» или «после» изменения.

transition_relation_name: Задает неквалифицированное имя, которое будет использоваться в теле триггера для обращения к соответствующему переходному отношению.

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

condition

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

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

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

Для триггеров ограничений проверка условия WHEN не откладывается, а выполняется сразу после обновления строки. Если условие не возвращает true, триггер не попадает в очередь отложенного выполнения.

function_name: Задает пользовательскую функцию, не принимающую аргументов и возвращающую тип trigger. Она будет выполнена при срабатывании триггера.
В синтаксисе CREATE TRIGGER ключевые слова FUNCTION и PROCEDURE считаются равнозначными, однако на самом деле должна указываться именно функция, а не процедура. Использование PROCEDURE здесь устарело и не рекомендуется.

arguments: Задает список аргументов (опциональный), разделенных запятыми, которые будут переданы функции при выполнении триггера. Аргументы представляют собой строковые литералы. Также можно указать простые имена и числовые значения — они будут преобразованы в строки. Ознакомьтесь с описанием языка реализации триггерной функции, чтобы узнать, как эти аргументы можно получить в ее теле — это может отличаться от обычной передачи аргументов.

Примечания

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

Чтобы удалить триггер, используйте команду DROP TRIGGER.

Создание триггера уровня строки на партиционированной таблице приводит к созданию идентичных «клонированных» триггеров на всех существующих партициях. Любые новые партиции, создаваемые или прикрепляемые позже, также получат такой же триггер. Если на дочерней партиции уже есть триггер с таким именем, произойдет ошибка, если не используется CREATE OR REPLACE TRIGGER — в этом случае старый триггер будет заменен клоном. При отсоединении партиции от родительской таблицы ее клонированные триггеры удаляются.

Триггер, заданный для конкретных столбцов (с помощью UPDATE OF column_name), срабатывает, когда хотя бы один из указанных столбцов указан в списке SET команды UPDATE. Значение столбца может измениться даже если триггер не сработал — если изменение произошло внутри триггера BEFORE UPDATE. Обратная ситуация тоже возможна: команда вроде UPDATE ... SET x = x ... вызовет триггер на столбец x, даже если фактическое значение не изменилось.

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

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

Иногда одна SQL-команда может вызвать срабатывание нескольких типов триггеров. Например, команда INSERT с ON CONFLICT DO UPDATE может вызвать как вставку, так и обновление — будут сработаны оба типа триггеров. Переходные отношения передаются в зависимости от типа события: триггер INSERT видит только вставленные строки, а триггер UPDATE — только обновленные.

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

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

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

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

В настоящее время OR REPLACE не поддерживается для триггеров ограничений.

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

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

Примеры

Выполнение функции 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();

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

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

CREATE TRIGGER в PostgreSQL реализует подмножество стандарта SQL. В настоящий момент отсутствуют следующие возможности:

Хотя имена переходных таблиц для триггеров AFTER задаются с помощью конструкции REFERENCING в стандартной форме, переменные строк в триггерах FOR EACH ROW не могут быть указаны через REFERENCING. Они доступны в зависимости от языка, на котором написана функция триггера, и это поведение фиксировано для каждого языка. Некоторые языки фактически ведут себя так, как если бы была указана конструкция REFERENCING OLD ROW AS OLD NEW ROW AS NEW.
Стандарт допускает использование переходных таблиц в триггерах UPDATE, определенных для конкретных столбцов, но тогда набор строк, доступных в переходных таблицах, должен зависеть от списка столбцов триггера. PostgreSQL это пока не реализует.
PostgreSQL позволяет выполнять в теле триггера только пользовательские функции. Стандарт SQL допускает выполнение других команд SQL, например CREATE TABLE, в качестве действия триггера. Эту особенность PostgreSQL легко обойти, создав пользовательскую функцию, которая выполняет нужные команды.

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

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

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

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

Команда CREATE CONSTRAINT TRIGGER, а также OR REPLACE — это расширения PostgreSQL, не предусмотренные стандартом SQL.

Синтаксис​

Описание​

Параметры​

Примечания​

Примеры​

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

Смотрите также​