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	Таблицы и сторонние таблицы	Таблицы, представления и сторонние таблицы
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. Для решения проблем, в которых, возможно, требуются такие триггеры, могут подойти правила и представления

Дополнительную информацию о триггерах см. в главе Триггеры.

---

Параметры

имя

Имя, задаваемое новому триггеру. Оно должно отличаться от имени любого другого триггера для той же таблицы. Это имя не может быть дополнено схемой, так как триггер наследует схему своей таблицы. Для триггера ограничения это имя также используется при изменении поведения триггера с помощью команды 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 не откладывается, а выполняется сразу после осуществления операции изменения строки. Если это условие не вычисляется в true, триггер не помещается в очередь для отложенного выполнения.

имя_функции

Функция, предоставленная пользователем, которая объявляется как функция, не принимающая аргументы и возвращающая тип 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, рассматриваемая условием, содержит текущее значение, которое, возможно, было изменено предыдущими триггерами. Кроме того, условию WHEN триггера BEFORE не разрешено проверять системные столбцы строки NEW (например, ctid), потому что они еще не были установлены.

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

В некоторых случаях одна команда 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 не поддерживается для триггеров ограничений.

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

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

---

Примеры

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

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

---

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

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

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

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

• QHB разрешает выполнять в качестве действия триггера только пользовательскую функцию. Стандарт же позволяет выполнять в качестве действия триггера ряд других команд SQL, таких как CREATE TABLE. Это ограничение нетрудно обойти, создав пользовательскую функцию, которая выполняет желаемые команды.

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

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

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

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

Вариант команды CREATE CONSTRAINT TRIGGER является расширением стандарта SQL, реализованным в QHB, как и параметр OR REPLACE.

---

См. также

ALTER TRIGGER, DROP TRIGGER, CREATE FUNCTION, SET CONSTRAINTS