CREATE TRIGGER

• Синтаксис
• Описание
• Параметры
• Примечания
• Примеры
• Совместимость
• См. также

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.

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

Кроме того, в определении триггера можно указать логическое условие 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.

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

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

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

В триггере AFTER условие WHEN вычисляется сразу после того, как происходит изменение строки, и это определяет, помещается ли событие в очередь для запуска триггера в конце оператора. Поэтому когда условие WHE триггера 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, то все генерируемые им операторы выполняются вместо исходного оператора, обращающегося к этому представлению, так что триггеры, которые будут запущены, являются триггерами для таблиц, к которым обращаются заменяющие операторы. Аналогично, если представление автоматически обновляется, то операция обрабатывается путем автоматического переписывания оператора в виде операции с базовой таблицей представления, так что запускаются триггеры уровня операторов базовой таблицы.

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

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

Примеры

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

См. также

ALTER TRIGGER, DROP TRIGGER, CREATE FUNCTION, SET CONSTRAINTS