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

Выполнение для каждой строки функции 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/RUST.

---

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

Команда 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