Триггеры DML
Триггеры DML — это хранимые процедуры особого типа, автоматически вступающие в силу, если происходит событие языка обработки данных DML, которое затрагивает таблицу или представление, определенное в триггере. События DML включают инструкции INSERT, UPDATE или DELETE. Триггеры DML можно использовать для применения бизнес-правил и целостности данных, запроса других таблиц и включения сложных инструкций Transact-SQL. Триггер и инструкция, при выполнении которой он срабатывает, считаются одной транзакцией, которую можно откатить назад внутри триггера. При обнаружении серьезной ошибки (например, нехватки места на диске) вся транзакция автоматически откатывается назад.
Преимущества триггеров DML
Триггеры DML аналогичны ограничениям в том, что могут предписывать целостность сущностей или целостность домена. Вообще говоря, целостность сущностей должна всегда предписываться на самом нижнем уровне с помощью индексов, являющихся частью ограничений PRIMARY KEY и UNIQUE или создаваемых независимо от ограничений. Целостность домена должна быть предписана через ограничения CHECK, а ссылочная целостность — через ограничения FOREIGN KEY. Триггеры DML наиболее полезны в тех случаях, когда функции ограничений не удовлетворяют функциональным потребностям приложения.
В следующем списке приведено сравнение триггеров DML с ограничениями и указано, чем триггеры DML лучше ограничений.
- Триггеры DML позволяют каскадно проводить изменения через связанные таблицы в базе данных; но эти изменения могут осуществляться более эффективно с использованием каскадных ограничений ссылочной целостности. Ограничения FOREIGN KEY могут проверить значения столбца только на предмет точного совпадения со значениями другого столбца, за исключением случаев, когда с помощью предложения REFERENCES задаются каскадные ссылочные действия.
- Для предотвращения случайных или неверных операций INSERT, UPDATE и DELETE и реализации других более сложных ограничений, чем те, которые определены при помощи ограничения CHECK. В отличие от ограничений CHECK, DML-триггеры могут ссылаться на столбцы других таблиц. Например, триггер может использовать инструкцию SELECT для сравнения вставленных или обновленных данных и выполнения других действий, например изменения данных или отображения пользовательского сообщения об ошибке.
- Чтобы оценить состояние таблицы до и после изменения данных и предпринять действия на основе этого различия.
- Несколько DML-триггеров одинакового типа (INSERT, UPDATE или DELETE) для таблицы позволяют предпринять несколько различных действий в ответ на одну инструкцию изменения данных.
- Ограничения могут сообщать об ошибках только с помощью соответствующих стандартных системных сообщений. Если для пользовательского приложения требуются более сложные методы управления ошибками и, соответственно, пользовательские сообщения, то необходимо использовать триггер.
- При использовании триггеров DML может произойти откат изменений, нарушающих ссылочную целостность, что приводит к запрету модификации данных. Подобные триггеры могут применяться при изменении внешнего ключа в случаях, когда новое значение не соответствует первичному ключу. Обычно в указанных случаях используются ограничения FOREIGN KEY.
- Если в таблице триггеров существуют ограничения, то их проверка осуществляется между выполнением триггеров INSTEAD OF и AFTER. В случае нарушения ограничений выполняется откат действий триггеров INSTEAD OF, а триггер AFTER не срабатывает.
Типы триггеров DML
Триггер AFTER
Триггеры AFTER выполняются после выполнения действий инструкции INSERT, UPDATE, MERGE или DELETE. Триггеры AFTER никогда не выполняются, если происходит нарушение ограничения, поэтому эти триггеры нельзя использовать для какой-либо обработки, которая могла бы предотвратить нарушение ограничения. Для каждой из операций INSERT, UPDATE или DELETE в указанной инструкции MERGE соответствующий триггер вызывается для каждой операции DML.
Триггер INSTEAD OF
Триггеры INSTEAD OF переопределяют стандартные действия инструкции, вызывающей триггер. Поэтому они могут использоваться для проверки на наличие ошибок или проверки значений в одном или нескольких столбцах и выполнения дополнительных действий перед вставкой, обновлением или удалением одной строки или нескольких строк. Например, если обновляемое значение в столбце почасовой оплаты в таблице учетной ведомости начинает превышать определенное значение, то с помощью этого триггера можно либо задать вывод сообщения об ошибке и откатить транзакцию, либо сделать вставку новой записи в след аудита до вставки записи в таблицу учетной ведомости. Главное преимущество триггеров INSTEAD OF в том, что они позволяют поддерживать обновления для таких представлений, которые обновлять невозможно. Например, в представлении, основанном на нескольких базовых таблицах, должен использоваться триггер INSTEAD OF для поддержки операций вставки, обновления и удаления, которые ссылаются на данные больше чем в одной таблице. Другое преимущество триггера INSTEAD OF состоит в том, что он обеспечивает логику кода, при которой можно отвергать одни части пакета и принимать другие.
Функциональность триггеров AFTER и INSTEAD OF сравнивается в следующей таблице.
Функция | Триггер AFTER | Триггер INSTEAD OF |
---|---|---|
Применимость | Таблицы | Таблицы и представления |
Количество триггеров на таблицу или представление | Несколько триггеров на одно запускающее триггеры действие (UPDATE, DELETE или INSERT). | Один триггер на одно запускающее триггеры действие (UPDATE, DELETE или INSERT). |
Каскадные ссылки | Нет ограничений. | Триггеры INSTEAD OF UPDATE и DELETE нельзя определять для таблиц, на которые распространяются каскадные ограничения ссылочной целостности. |
Выполнение | После: |
Декларативные ссылочные действия.
Создание таблицinserted и deleted .
Вместо: действие, запускающее триггер
Триггеры CLR
Триггер CLR может быть либо триггером AFTER, либо триггером INSTEAD OF. Триггер CLR может также являться триггером DDL. Вместо выполнения хранимой процедуры Transact-SQL триггер CLR выполняет один или несколько методов, написанных в управляемом коде, которые являются членами сборки, созданной в .NET Framework и переданной в SQL Server.
Связанные задачи
Задача | Раздел |
---|---|
Описывает, как создать триггер DML. | Создание триггеров DML |
Описывает, как создать триггер CLR. | Создание триггеров CLR |
Описывает, как создать триггер DML для выполнения и однострочных, и многострочных операций модификации данных. | Создание триггеров DML для обработки нескольких строк данных |
Описывает, как вкладывать триггеры. | Создание вложенных триггеров |
Описывает, как указывать порядок, в котором активируются триггеры AFTER. | Указание первого и последнего триггеров |
Описывает, как использовать специальные таблицы inserted и deleted в коде триггера. | Использование вставленных и удаленных таблиц |
Описывает, как изменить или переименовать триггер DML. | Изменение или переименование триггеров DML |
Описывает, как просматривать сведения о триггерах DML. | Получение сведений о триггерах DML |
Описывает, как удалять или отключать триггеры DML. | Удаление или отключение триггеров DML |
Описывает, как управлять безопасностью триггеров. | Управление безопасностью триггеров |
Триггеры
Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE.
Формальное определение триггера:
CREATE TRIGGER имя_триггера ON [INSERT | UPDATE | DELETE] AS выражения_sql
Для создания триггера применяется выражение CREATE TRIGGER , после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.
Каждый триггер ассоциируется с определенной таблицей или представлением, имя которых указывается после слова ON .
Затем устанавливается тип триггера. Мы можем использовать один из двух типов:
- AFTER : выполняется после выполнения действия. Определяется только для таблиц.
- INSTEAD OF : выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений
После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .
Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.
И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.
Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:
CREATE DATABASE productdb; GO USE productdb; CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL );
Определим триггер, который будет срабатывать при добавлении и обновлении данных:
USE productdb; GO CREATE TRIGGER Products_INSERT_UPDATE ON Products AFTER INSERT, UPDATE AS UPDATE Products SET Price = Price + Price * 0.38 WHERE Id FROM inserted)
Допустим, в таблице Products хранятся данные о товарах. Но цена товара нередко содержит различные надбавки типа налога на добавленную стоимость, налога на добавленную коррупцию и так далее. Человек, добавляющий данные, может не знать все эти тонкости с налоговой базой, и он определяет чистую цену. С помощью триггера мы можем поправить цену товара на некоторую величину.
Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.
И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:
Удаление триггера
Для удаления триггера необходимо применить команду DROP TRIGGER :
DROP TRIGGER Products_INSERT_UPDATE
Отключение триггера
Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :
DISABLE TRIGGER Products_INSERT_UPDATE ON Products
А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :
ENABLE TRIGGER Products_INSERT_UPDATE ON Products
Почему вызов триггеров осуществляется автоматически
Триггер является указанием, что база данных должна автоматически выполнить заданную функцию, всякий раз когда выполнен определённый тип операции. Триггеры можно использовать с таблицами (секционированными и обычными), с представлениями и с внешними таблицами.
Для обычных и сторонних таблиц можно определять триггеры, которые будут срабатывать до или после любой из команд INSERT , UPDATE или DELETE ; либо один раз для каждой модифицируемой строки, либо один раз для оператора SQL . Триггеры на UPDATE можно установить так, чтобы они срабатывали, только когда в предложении SET оператора UPDATE упоминаются определённые столбцы. Также триггеры могут срабатывать для операторов TRUNCATE . Если происходит событие триггера, для обработки этого события в установленный момент времени вызывается функция триггера.
Для представлений триггеры могут быть определены для выполнения вместо операций INSERT , UPDATE и DELETE . Такие триггеры INSTEAD OF вызываются единожды для каждой строки, которая должна быть изменена в этом представлении. Именно функция триггера отвечает за то, чтобы произвести необходимые изменения в нижележащих базовых таблицах представления и должным образом возвращать изменённые строки, чтобы они появлялись в представлении. Триггеры для представлений тоже могут быть определены так, что они будут выполняться единожды для всего оператора SQL , до или после операций INSERT , UPDATE или DELETE . Однако такие триггеры срабатывают, только если для представления определён триггер INSTEAD OF . В противном случае все операторы, обращающиеся к представлению, должны быть переписаны в виде операторов, обращающихся к нижележащим базовым таблицам, и тогда будут срабатывать триггеры, установленные для этих таблиц.
Триггерная функция должна быть создана до триггера. Она должна быть объявлена без аргументов и возвращать тип trigger . (Триггерная функция получает данные на вход посредством специально переданной структуры TriggerData , а не в форме обычных аргументов.)
После создания триггерной функции создаётся триггер с помощью CREATE TRIGGER . Одна и та же триггерная функция может быть использована для нескольких триггеров.
PostgreSQL предлагает как построчные, так и операторные триггеры. В случае построчного триггера триггерная функция вызывается один раз для каждой строки, затронутой оператором, запустившим триггер. Операторный же триггер, напротив, вызывается только один раз при выполнении соответствующего оператора, независимо от количества строк, которые он затрагивает. В частности оператор, который не затрагивает никаких строк, всё равно приведёт к срабатыванию операторного триггера. Эти два типа триггеров также называют триггерами уровня строк и триггерами уровня оператора, соответственно. Триггеры на TRUNCATE могут быть определены только на уровне оператора, а не на уровне строк.
Триггеры также классифицируются в соответствии с тем, срабатывают ли они до, после или вместо операции. Они называются триггерами BEFORE , AFTER и INSTEAD OF , соответственно. Триггеры BEFORE уровня оператора срабатывают до того, как оператор начинает делать что-либо, тогда как триггеры AFTER уровня оператора срабатывают в самом конце работы оператора. Эти типы триггеров могут быть определены для таблиц, представлений или сторонних таблиц. Триггеры BEFORE уровня строки срабатывают непосредственно перед обработкой конкретной строки, в то время как триггеры AFTER уровня строки срабатывают в конце работы всего оператора (но до любого из триггеров AFTER уровня оператора). Эти типы триггеров могут определяться только для таблиц, в том числе сторонних, но не для представлений. Триггеры INSTEAD OF могут определяться только для представлений и только на уровне строк: они срабатывают для каждой строки сразу после того как строка представления идентифицирована как подлежащая обработке.
Если триггер AFTER — это триггер ограничения, его выполнение может быть отложено не до конца работы оператора, а до конца транзакции. В любом случае триггер выполняется в рамках той же транзакции, к которой относится вызвавший его оператор, поэтому если или оператор, или триггер вызывает ошибку, оба действия отменяются.
Оператор, нацеленный на родительскую таблицу в иерархии наследования или секционирования, не вызывает срабатывания триггеров уровня оператора для задействованных дочерних таблиц; срабатывать будут только такие триггеры для родительской таблицы. Однако если для этих дочерних таблиц установлены триггеры уровня строк, они будут срабатывать.
Если запрос INSERT содержит предложение ON CONFLICT DO UPDATE , возможно совместное применение и триггеров уровня строк BEFORE INSERT , и триггеров уровня строк BEFORE UPDATE , которое отразится в окончательном состоянии изменяемой строки, если в запросе задействуются столбцы EXCLUDED . При этом обращение к EXCLUDED не обязательно должно иметь место в обоих наборах триггеров BEFORE на уровне строк. Следует рассмотреть возможность получения неожиданного результата, когда имеются и триггеры BEFORE INSERT , и BEFORE UPDATE на уровне строки, и они вместе модифицируют добавляемую/изменяемую строку (проблемы возможны, даже если изменения более или менее равнозначные, но при этом не идемпотентные). Заметьте, что триггеры UPDATE уровня оператора вызываются при ON CONFLICT DO UPDATE независимо от того, будут ли изменены какие-либо строки в результате UPDATE (и даже в случае, когда альтернативный путь UPDATE вообще не выбирается). При выполнении запроса INSERT с предложением ON CONFLICT DO UPDATE сначала выполняются триггеры BEFORE INSERT , затем триггеры BEFORE UPDATE , потом триггеры AFTER UPDATE и, наконец, AFTER INSERT (речь идёт о триггерах на уровне операторов).
Если оператор UPDATE в секционированной таблице должен переместить строку в другую секцию, это перемещение реализуется в результате выполнения DELETE в исходной секции и последующего INSERT в новой секции. При этом в исходной секции срабатывают все триггеры BEFORE UPDATE и BEFORE DELETE уровня строк. Затем в целевой секции срабатывают все триггеры BEFORE INSERT уровня строк. Следует иметь в виду, что в случаях, когда все эти триггеры модифицируют перемещаемую строку, полученный результат может быть неожиданным. Если рассматривать триггеры AFTER ROW , то применяться будут триггеры AFTER DELETE и AFTER INSERT , но не триггеры AFTER UPDATE , так как команда UPDATE заменяется на DELETE и INSERT . Если же рассматривать триггеры уровня операторов, ни триггеры DELETE , ни триггеры INSERT не будут срабатывать, даже если производится перемещение строк; сработают только триггеры UPDATE , установленные в целевой таблице оператора UPDATE .
Триггерные функции, вызываемые триггерами операторов, должны всегда возвращать NULL . Триггерные функции, вызываемые триггерами строк, могут вернуть строку таблицы (значение типа HeapTuple ). У триггера уровня строки, срабатывающего до операции, есть следующий выбор:
Можно вернуть NULL , чтобы пропустить операцию для текущей строки. Это указывает исполнителю запросов, что не нужно выполнять операцию со строкой вызвавшей триггер (вставку, изменение или удаление конкретной строки в таблице).
Если в триггере BEFORE уровня строки не планируется использовать любой из этих вариантов, то нужно аккуратно вернуть в качестве результата ту же строку, которая была передана на вход (то есть строку NEW для триггеров INSERT и UPDATE , или строку OLD для триггеров DELETE ).
Триггер уровня строки INSTEAD OF должен вернуть либо NULL , чтобы указать, что он не модифицирует базовые таблицы представления, либо он должен вернуть строку представления, полученную на входе (строку NEW для операций INSERT и UPDATE или строку OLD для операций DELETE ). Отличное от NULL возвращаемое значение сигнализирует, что триггер выполнил необходимые изменения данных в представлении. Это приведёт к увеличению счётчика количества строк, затронутых командой. Для операций INSERT и UPDATE (и только для них) триггер может изменить строку NEW перед тем как её вернуть. В результате будут изменены данные, возвращаемые INSERT RETURNING или UPDATE RETURNING , что полезно, когда представление должно возвращать не те данные, что были получены.
Возвращаемое значение игнорируется для триггеров уровня строки, вызываемых после операции, поэтому они могут возвращать NULL .
Генерируемые столбцы заслуживают отдельного внимания. Сохраняемые генерируемые столбцы вычисляются после триггеров BEFORE и перед триггерами AFTER . Таким образом, в триггерах AFTER можно наблюдать сгенерированное значение. В триггерах BEFORE строка OLD , как можно было ожидать, содержит предыдущее значение, однако в строке NEW ещё не содержится новое сгенерированное значение, и обращаться к нему не следует. На уровне языка C содержимое столбца в этот момент считается неопределённым; более высокоуровневые языки должны блокировать обращения к сохраняемому генерируемому столбцу в строке NEW внутри триггера BEFORE . Изменённые в триггере BEFORE значения генерируемого столбца игнорируются и будут перезаписаны.
Если есть несколько триггеров на одно и то же событие для одной и той же таблицы, то они будут вызываться в алфавитном порядке по имени триггера. Для триггеров BEFORE и INSTEAD OF потенциально изменённая строка, возвращаемая одним триггером, становится входящей строкой для следующего триггера. Если любой из триггеров BEFORE или INSTEAD OF возвращает NULL , операция для этой строки прекращается и последующие триггеры (для этой строки) не срабатывают.
В определении триггера можно указать логическое условие WHEN , которое будет проверяться, чтобы посмотреть, нужно ли запускать триггер. В триггерах уровня строки в условии WHEN можно проверять старые и/или новые значения столбцов строки. (В триггерах уровня оператора также можно использовать условие WHEN , хотя в этом случае это не так полезно.) В триггерах BEFORE условие WHEN вычисляется непосредственно перед тем, как триггерная функция будет выполнена, поэтому использование WHEN существенно не отличается от выполнения той же проверки в самом начале триггерной функции. Однако в триггерах AFTER условие WHEN вычисляется сразу после обновления строки и от этого зависит, будет ли поставлено в очередь событие запуска триггера в конце оператора или нет. Поэтому, когда условие WHEN в триггере AFTER не возвращает истину, не требуется ни постановка события в очередь, ни повторная выборка этой строки в конце оператора. Это может существенно ускорить работу операторов, изменяющих большое количество строк, с триггером, который должен сработать только для нескольких. В триггерах INSTEAD OF не поддерживается использование условий WHEN .
Как правило, триггеры BEFORE уровня строки используются для проверки или модификации данных, которые будут вставлены или изменены. Например, триггер BEFORE можно использовать для вставки текущего времени в столбец timestamp или проверки, что два элемента строки согласованы между собой. Триггеры AFTER уровня строки наиболее разумно использовать для каскадного обновления данных в других таблицах или проверки согласованности сделанных изменений с данными в других таблицах. Причина для такого разделения работы в том, что триггер AFTER видит окончательное значение строки, в то время как для триггера BEFORE это не так, ведь могут быть другие триггеры BEFORE , которые сработают позже. Если нет особых причин для выбора между триггерами BEFORE или AFTER , то триггер BEFORE предпочтительнее, так как не требует сохранения информации об операции до конца работы оператора.
Если триггерная функция выполняет команды SQL, эти команды могут заново запускать триггеры. Это известно как каскадные триггеры. Прямых ограничений на количество каскадных уровней не существует. Вполне возможно, что каскадные вызовы приведут к рекурсивному срабатыванию одного и того же триггера. Например, в триггере INSERT может выполняться команда, которая добавляет строку в эту же таблицу, тем самым опять вызывая триггер на INSERT . Обязанность программиста не допускать бесконечную рекурсию в таких случаях.
При определении триггера можно указывать аргументы. Цель включения аргументов в определение триггера в том, чтобы позволить разным триггерам с аналогичными требованиями вызывать одну и ту же функцию. В качестве примера можно создать обобщенную триггерную функцию, которая принимает два аргумента с именами столбцов и записывает текущего пользователя в первый аргумент и текущий штамп времени во второй. При правильном написании такая триггерная функция будет независима от конкретной таблицы, для которой она будет запускаться. Таким образом, одна и та же функция может использоваться при выполнении INSERT в любую таблицу с соответствующими столбцами, чтобы, например, автоматически отслеживать создание записей в транзакционной таблице. Для триггеров UPDATE аргументы также могут использоваться для отслеживания последних сделанных изменений.
У каждого языка программирования, поддерживающего триггеры, есть свой собственный метод доступа из триггерной функции к входным данным триггера. Входные данные триггера включают в себя тип события (например, INSERT или UPDATE ), а также любые аргументы, перечисленные в CREATE TRIGGER . Для триггеров уровня строки входные данные также включают строку NEW для триггеров INSERT и UPDATE и/или строку OLD для триггеров UPDATE и DELETE .
Триггеры уровня оператора по умолчанию не имеют возможностей для проверки отдельных строк, модифицированных оператором. Но триггер AFTER STATEMENT может запросить создание для него переходных таблиц, чтобы ему были доступны наборы затрагиваемых операцией строк. Триггерам AFTER ROW также могут предоставляться переходные таблицы, чтобы они могли видеть все изменения в таблице, а не только изменения в отдельных строках, для которых они срабатывают. Метод обращения к переходным таблицам определяется применяемым языком программирования, но обычно переходные таблицы представляются как временные таблицы только для чтения, к которым в триггерной функции можно обращаться, выполняя SQL-команды.
Пред. | Наверх | След. |
Глава 39. Триггеры | Начало | 39.2. Видимость изменений в данных |
37.1. Обзор поведения триггеров#
Триггер — это спецификация, согласно которой база данных должна автоматически выполнять определенную функцию при выполнении определенного типа операции. Триггеры могут быть привязаны к таблицам (разделенным или нет), представлениям и внешним таблицам. На таблицах и внешних таблицах можно определить триггеры для выполнения операций INSERT , UPDATE или DELETE либо перед, либо после операции, либо один раз для каждой измененной строки, либо один раз для SQL оператора. Триггеры UPDATE могут также быть настроены на срабатывание только в случае, если определенные столбцы указаны в SET части оператора UPDATE . Триггеры также могут срабатывать для операторов TRUNCATE . Если происходит событие триггера, вызывается функция триггера в нужное время для обработки события. На представлениях можно определить триггеры для выполнения вместо операций INSERT , UPDATE или DELETE . Такие триггеры INSTEAD OF срабатывают один раз для каждой строки, которую необходимо изменить в представлении. Обязанностью функции триггера является выполнение необходимых изменений в базовой таблице(ах) представления и, при необходимости, возврат измененной строки в виде, в котором она будет отображаться в представлении. Триггеры на представлениях также могут быть определены для выполнения один раз для каждого SQL оператора, перед или после операций INSERT , UPDATE или DELETE . Однако такие триггеры срабатывают только в том случае, если на представлении также определен триггер INSTEAD OF . В противном случае любой оператор, направленный на представление, должен быть переписан в оператор, воздействующий на его базовую таблицу(ы), и затем триггеры, которые будут срабатывать, будут привязаны к базовой таблице(ам). Функция триггера должна быть определена до создания самого триггера. Функция триггера должна быть объявлена как функция, не принимающая аргументов и возвращающая тип trigger . (Функция триггера получает свои входные данные через специально передаваемую структуру TriggerData , а не в виде обычных аргументов функции). После создания подходящей функции триггера, триггер устанавливается с помощью CREATE TRIGGER . Одну и ту же функцию триггера можно использовать для нескольких триггеров. Tantor SE предлагает как триггеры на строку, так и триггеры на операцию. С триггером на строку, функция триггера вызывается один раз для каждой строки, которая затрагивается операцией, вызвавшей триггер. В отличие от этого, триггер на операцию вызывается только один раз при выполнении соответствующей операции, независимо от количества строк, затронутых этой операцией. В частности, операция, которая не затрагивает ни одной строки, все равно приведет к выполнению всех применимых триггеров на операцию. Эти два типа триггеров иногда называются триггеры на уровне строки и триггеры на уровне операции соответственно. Триггеры на TRUNCATE могут быть определены только на уровне операции, а не на уровне строки. Триггеры также классифицируются в зависимости от того, срабатывают ли они перед, после или вместо операции. Они называются соответственно триггерами BEFORE , AFTER и INSTEAD OF . Триггеры BEFORE на уровне оператора срабатывают перед началом выполнения оператора, а триггеры AFTER на уровне оператора срабатывают в самом конце оператора. Эти типы триггеров могут быть определены для таблиц, представлений или внешних таблиц. Триггеры BEFORE на уровне строки срабатывают непосредственно перед выполнением операции над определенной строкой, а триггеры AFTER на уровне строки срабатывают в конце оператора (но перед любыми триггерами AFTER на уровне оператора). Эти типы триггеров могут быть определены только для таблиц и внешних таблиц, но не для представлений. Триггеры INSTEAD OF могут быть определены только для представлений и только на уровне строки; они срабатывают непосредственно при идентификации каждой строки в представлении, которая должна быть обработана. Выполнение триггера AFTER может быть отложено до конца транзакции, а не до конца оператора, если он был определен как триггер-ограничение. Во всех случаях триггер выполняется в рамках той же транзакции, что и оператор, вызвавший его, поэтому если либо оператор, либо триггер вызывают ошибку, изменения обоих будут отменены. Выполнение оператора, который нацелен на родительскую таблицу в иерархии наследования или разделения, не приводит к запуску триггеров на уровне оператора для затронутых дочерних таблиц; запускаются только триггеры на уровне оператора родительской таблицы. Однако, будут запущены триггеры на уровне строки для любых затронутых дочерних таблиц. Если INSERT содержит фразу ON CONFLICT DO UPDATE , то возможно, что эффекты триггеров BEFORE INSERT на уровне строки и триггеров BEFORE UPDATE на уровне строки могут быть применены таким образом, что это будет видно из конечного состояния обновленной строки, если есть ссылка на столбец EXCLUDED . Для выполнения обоих наборов триггеров на уровне строки не обязательно иметь ссылку на столбец EXCLUDED , однако следует учитывать возможность неожиданных результатов, когда есть и триггеры BEFORE INSERT , и триггеры BEFORE UPDATE , которые изменяют вставляемую/обновляемую строку (это может быть проблематично, даже если модификации более или менее эквивалентны, если они не являются идемпотентными). Обратите внимание, что триггеры UPDATE на уровне оператора выполняются при указании фразы ON CONFLICT DO UPDATE , независимо от того, были ли какие-либо строки затронуты UPDATE (и независимо от того, был ли выбран альтернативный путь UPDATE ). INSERT с фразой ON CONFLICT DO UPDATE будет выполнять триггеры INSERT на уровне оператора BEFORE сначала, затем триггеры UPDATE на уровне оператора BEFORE , за которыми следуют триггеры UPDATE на уровне оператора AFTER и, наконец, триггеры INSERT на уровне оператора AFTER . Если UPDATE на разделенной таблице приводит к перемещению строки в другой раздел, это будет выполнено как DELETE из исходного раздела, за которым следует INSERT в новый раздел. В этом случае все триггеры BEFORE UPDATE на уровне строки и все триггеры на уровне строки BEFORE DELETE будут запущены на исходном разделе. Затем все триггеры BEFORE INSERT на уровне строки будут запущены на целевом разделе. Необходимо учитывать возможность неожиданных результатов, когда все эти триггеры влияют на перемещаемую строку. Что касается триггеров AFTER ROW , триггеры AFTER DELETE и AFTER INSERT применяются; но триггеры AFTER UPDATE не применяются, потому что UPDATE был преобразован в DELETE и INSERT . Что касается триггеров на уровне оператора, ни один из триггеров DELETE или INSERT не запускается, даже если происходит перемещение строк; будут запущены только триггеры UPDATE , определенные на целевой таблице, используемой в операторе UPDATE . Вместо этого не определены отдельные триггеры для MERGE . Вместо этого срабатывают триггеры на уровне оператора или строки для UPDATE , DELETE и INSERT в зависимости от (для триггеров на уровне оператора) указанных действий в запросе MERGE и (для триггеров на уровне строки) выполненных действий. Во время выполнения команды MERGE запускаются триггеры уровня оператора BEFORE и AFTER для событий, указанных в действиях команды MERGE , независимо от того, выполняется ли действие в конечном итоге или нет. Это аналогично оператору UPDATE , который не обновляет ни одной строки, но все равно запускает триггеры уровня оператора. Триггеры уровня строки запускаются только при фактическом обновлении, вставке или удалении строки. Поэтому вполне допустимо, что триггеры уровня оператора запускаются для определенных типов действий, но триггеры уровня строки не запускаются для того же типа действий. Функции триггеров, вызываемые триггерами на уровне оператора, всегда должны возвращать NULL . Функции триггеров, вызываемые триггерами на уровне строки, могут вернуть строку таблицы (значение типа HeapTuple ) вызывающему исполнителю, если они выберут. Триггер на уровне строки, сработавший перед операцией, имеет следующие варианты выбора:
- Он может вернуть NULL , чтобы пропустить операцию для текущей строки. Это указывает исполнителю не выполнять операцию на уровне строки, которая вызвала триггер (вставку, изменение или удаление определенной строки таблицы).
- Для триггеров на уровне строк, выполняющих команды INSERT и UPDATE , возвращаемая строка становится строкой, которая будет вставлена или заменит обновляемую строку. Это позволяет функции триггера изменять вставляемую или обновляемую строку.
Триггер BEFORE на уровне строки, который не предполагает вызова ни одного из этих поведений, должен быть осторожным и возвращать в качестве результата ту же самую строку, которая была передана (то есть строку NEW для триггеров INSERT и UPDATE , строку OLD для триггеров DELETE ).
Триггер на уровне строки INSTEAD OF должен либо возвращать NULL , чтобы указать, что он не изменял данные из базовых таблиц представления, либо возвращать строку представления, которая была передана (строку NEW для операций INSERT и UPDATE , или строку OLD для операций DELETE ). Ненулевое значение используется для сигнализации о том, что триггер выполнил необходимые изменения данных в представлении. Это приведет к увеличению количества затронутых командой строк. Только для операций INSERT и UPDATE триггер может изменять строку NEW перед ее возвратом. Это изменит данные, возвращаемые при использовании INSERT RETURNING или UPDATE RETURNING , и полезно, когда представление не будет показывать точно те же данные, которые были предоставлены.
Возвращаемое значение игнорируется для триггеров на уровне строк, срабатывающих после операции, поэтому они могут возвращать NULL .
Некоторые соображения применяются к сгенерированным столбцам. Хранимые вычисляемые столбцы вычисляются после триггеров BEFORE и перед триггерами AFTER . Поэтому сгенерированное значение можно проверить в триггерах AFTER . В триггерах BEFORE старое сгенерированное значение содержится в строке OLD , как и ожидается, но строка NEW еще не содержит нового сгенерированного значения и к ней не следует обращаться. В интерфейсе на языке C содержимое столбца в этот момент не определено; язык программирования более высокого уровня должен предотвращать доступ к хранимому вычисляемому столбцу в строке NEW в триггере BEFORE . Изменения значения сгенерированного столбца в триггере BEFORE игнорируются и будут перезаписаны.
Если для одного и того же события на одной и той же связи определено более одного триггера, триггеры будут запускаться в алфавитном порядке по имени триггера. В случае триггеров BEFORE и INSTEAD OF измененная, возможно, строка, возвращаемая каждым триггером, становится входными данными для следующего триггера. Если какой-либо триггер BEFORE или INSTEAD OF возвращает NULL , операция отменяется для этой строки, и последующие триггеры не запускаются (для этой строки).
Определение триггера также может указывать логическое условие WHEN , которое будет проверяться для определения, должен ли триггер срабатывать. В триггерах на уровне строк условие WHEN может проверять старые и/или новые значения столбцов строки. (Триггеры на уровне операторов также могут иметь условия WHEN , хотя эта функция для них не так полезна). В триггере BEFORE условие WHEN вычисляется непосредственно перед выполнением или планируемым выполнением функции, поэтому использование WHEN не отличается от проверки того же условия в начале функции триггера. Однако в триггере AFTER условие WHEN вычисляется сразу после обновления строки и определяет, должно ли событие быть поставлено в очередь для срабатывания триггера в конце оператора. Поэтому, когда условие WHEN триггера AFTER не возвращает истинное значение, необходимо не ставить событие в очередь и не повторно извлекать строку в конце оператора. Это может привести к значительному увеличению скорости выполнения операторов, модифицирующих много строк, если триггер должен срабатывать только для нескольких строк. Триггеры INSTEAD OF не поддерживают условия WHEN .
Обычно, триггеры на уровне строки BEFORE используются для проверки или изменения данных, которые будут вставлены или обновлены. Например, триггер BEFORE может использоваться для вставки текущего времени в столбец timestamp или для проверки согласованности двух элементов строки. Триггеры на уровне строки AFTER наиболее разумно использовать для передачи обновлений в другие таблицы или для проверки согласованности с другими таблицами. Причина такого разделения труда заключается в том, что триггер AFTER может быть уверен, что видит окончательное значение строки, в то время как триггер BEFORE не может быть уверен; после него могут сработать другие триггеры BEFORE . Если у вас нет конкретной причины сделать триггер BEFORE или AFTER , то вариант BEFORE более эффективен, так как информация о операции не должна быть сохранена до конца оператора.
Если функция триггера выполняет SQL-команды, то эти команды могут снова запускать триггеры. Это известно как каскадные триггеры. Нет прямого ограничения на количество уровней каскада. Возможно, каскады могут вызывать рекурсивное выполнение одного и того же триггера; например, триггер INSERT может выполнить команду, которая вставляет дополнительную строку в ту же таблицу, вызывая срабатывание триггера INSERT снова. Ответственность за избегание бесконечной рекурсии в таких сценариях лежит на программисте триггера.
При определении триггера можно указать аргументы для него. Цель включения аргументов в определение триггера заключается в том, чтобы позволить различным триггерам с похожими требованиями вызывать одну и ту же функцию. Например, может быть обобщенная функция триггера, которая принимает в качестве аргументов два имена столбцов и помещает текущего пользователя в один столбец и текущую метку времени в другой. Правильно написанная такая функция триггера будет независима от конкретной таблицы, на которой она срабатывает. Таким образом, одну и ту же функцию можно использовать для событий INSERT на любой таблице с подходящими столбцами, чтобы автоматически отслеживать создание записей в таблице транзакций, например. Она также может использоваться для отслеживания событий последнего обновления, если определена как триггер UPDATE .
Каждый язык программирования, поддерживающий триггеры, имеет свой собственный метод для предоставления входных данных триггера функции триггера. Эти входные данные включают тип события триггера (например, INSERT или UPDATE ), а также любые аргументы, указанные в CREATE TRIGGER . Для триггера на уровне строки входные данные также включают строку NEW для триггеров INSERT и UPDATE , а также строку OLD для триггеров UPDATE и DELETE .
По умолчанию, триггеры на уровне оператора не имеют возможности анализировать отдельные измененные строки. Однако, триггер AFTER STATEMENT может запросить создание таблиц перехода, чтобы сделать наборы затронутых строк доступными для триггера. Триггеры AFTER ROW также могут запросить таблицы перехода, чтобы видеть общие изменения в таблице, а также изменения в отдельной строке, для которой они в данный момент запускаются. Метод анализа таблиц перехода снова зависит от используемого языка программирования, но типичным подходом является использование таблиц перехода как временных таблиц только для чтения, к которым можно обращаться с помощью SQL-команд, выполняемых внутри функции триггера.
Назад | Наверх | Далее |
Глава 37. Триггеры | Начало | 37.2. Видимость изменений данных |