25.3.1 Trigger Syntax and Examples

RU

EN

RU EN

CREATE TRIGGER — создать триггер

Что такое триггер?

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

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

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

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

Триггер должен быть связан с указанной таблицей, представлением (псевдотаблицей) или внешней таблицей. Он запускает свою часть кода только при выполнении операций с этой сущностью — INSERT, UPDATE, DELETE или TRUNCATE. В зависимости от требований мы можем запускать триггер до, после или вместо события/операции.

Но все же триггеры SQL сохраняют свое значение. Когда я работал над нестандартной ERP-системой, триггеры оказались неоценимым инструментом. При построении сильно ориентированного на данные ПО, особенно с данными в финансовой сфере, где точность является главным требованием, вы с большей вероятностью увидите, что данные обрабатываются непосредственно на более низком уровне.

В этой статье я поделюсь информацией о том, как эффективно использовать триггеры SQL.

A trigger is a named database object that is associated with a
table, and that activates when a particular event occurs for the
table. Some uses for triggers are to perform checks of values to be
inserted into a table or to perform calculations on values involved
in an update.

A trigger is defined to activate when a statement inserts, updates,
or deletes rows in the associated table. These row operations are
trigger events. For example, rows can be inserted by
INSERT or LOAD
DATA statements, and an insert trigger activates for each
inserted row. A trigger can be set to activate either before or
after the trigger event. For example, you can have a trigger
activate before each row that is inserted into a table or after each
row that is updated.

MySQL triggers activate only for changes made to tables by SQL
statements. This includes changes to base tables that underlie
updatable views. Triggers do not activate for changes to tables
made by APIs that do not transmit SQL statements to the MySQL
Server. This means that triggers are not activated by updates made
using the NDB API.

Triggers are not activated by changes in
INFORMATION_SCHEMA or
performance_schema tables. Those tables are
actually views and triggers are not permitted on views.

Additional Resources

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.

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

Кроме того, в определении триггера можно указать логическое условие WHEN, которое определит, вызывать триггер или нет. В триггерах на уровне строк условия WHEN могут проверять старые и/или новые значения столбцов в строке. Триггеры на уровне оператора так же могут содержать условие WHEN, хотя для них это не столь полезно, так как в этом условии нельзя ссылаться на какие-либо значения в таблице.

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

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

Когда указывается REFERENCING, для триггера собираются переходные отношения, представляющие собой множества строк, включающие все строки, которые были добавлены, удалены или изменены текущим оператором SQL. Это позволяет триггеру наблюдать общую картину того, что сделал оператор, а не только одну строку за другой. Это указание допускается только для триггера AFTER, не являющегося триггером ограничения; кроме того, если это триггер для UPDATE, у него должен отсутствовать список имён_столбцов. Указание OLD TABLE может быть задано только один раз и только для триггера, который может срабатывать при UPDATE или DELETE; оно создаёт переходное отношение, содержащее образы-до-изменения всех строк, модифицированных или удалённых оператором. Указание NEW TABLE, подобным образом, может быть задано только единожды и только для триггера, который может срабатывать для UPDATE или INSERT; оно создаёт переходное отношение, содержащее образы-после-изменения всех строк, модифицированных или добавленных оператором.

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

За дополнительными сведениями о триггерах обратитесь к Главе 39.

1 Trigger Syntax and Examples

To create a trigger or drop a trigger, use the
CREATE TRIGGER or
DROP TRIGGER statement, described
in Section 13.1.22, “CREATE TRIGGER Statement”, and
Section 13.1.34, “DROP TRIGGER Statement”.

Here is a simple example that associates a trigger with a table,
to activate for INSERT operations.
The trigger acts as an accumulator, summing the values inserted
into one of the columns of the table.

The CREATE TRIGGER statement
creates a trigger named ins_sum that is
associated with the account table. It also
includes clauses that specify the trigger action time, the
triggering event, and what to do when the trigger activates:

• The keyword BEFORE indicates the trigger
  action time. In this case, the trigger activates before each
  row inserted into the table. The other permitted keyword here
  is AFTER.
• The keyword INSERT indicates the trigger
  event; that is, the type of operation that activates the
  trigger. In the example, INSERT
  operations cause trigger activation. You can also create
  triggers for DELETE and
  UPDATE operations.

To use the trigger, set the accumulator variable to zero, execute
an INSERT statement, and then see
what value the variable has afterward:

To destroy the trigger, use a DROP
TRIGGER statement. You must specify the schema name if
the trigger is not in the default schema:

If you drop a table, any triggers for the table are also dropped.

Trigger names exist in the schema namespace, meaning that all
triggers must have unique names within a schema. Triggers in
different schemas can have the same name.

This trigger, ins_transaction, is similar to
ins_sum but accumulates deposits and
withdrawals separately. It has a PRECEDES
clause that causes it to activate before
ins_sum; without that clause, it would activate
after ins_sum because it is created after
ins_sum.

Within the trigger body, the OLD and
NEW keywords enable you to access columns in
the rows affected by a trigger. OLD and
NEW are MySQL extensions to triggers; they are
not case-sensitive.

In an INSERT trigger, only
NEW.col_name can be
used; there is no old row. In a DELETE trigger,
only OLD.col_name
can be used; there is no new row. In an UPDATE
trigger, you can use
OLD.col_name to
refer to the columns of a row before it is updated and
NEW.col_name to
refer to the columns of the row after it is updated.

A column named with OLD is read only. You can
refer to it (if you have the SELECT
privilege), but not modify it. You can refer to a column named
with NEW if you have the
SELECT privilege for it. In a
BEFORE trigger, you can also change its value
with SET NEW.col_name =
value if you have the
UPDATE privilege for it. This means
you can use a trigger to modify the values to be inserted into a
new row or used to update a row. (Such a SET
statement has no effect in an AFTER trigger
because the row change has already occurred.)

In a BEFORE trigger, the NEW
value for an AUTO_INCREMENT column is 0, not
the sequence number that is generated automatically when the new
row actually is inserted.

It can be easier to define a stored procedure separately and then
invoke it from the trigger using a simple
CALL statement. This is also
advantageous if you want to execute the same code from within
several triggers.

There are limitations on what can appear in statements that a
trigger executes when activated:

See also Section 25.8, “Restrictions on Stored Programs”.

• If a BEFORE trigger fails, the operation on
  the corresponding row is not performed.
• A BEFORE trigger is activated by the
  to insert or modify the row,
  regardless of whether the attempt subsequently succeeds.
• An AFTER trigger is executed only if any
  BEFORE triggers and the row operation
  execute successfully.
• An error during either a BEFORE or
  AFTER trigger results in failure of the
  entire statement that caused trigger invocation.
• For transactional tables, failure of a statement should cause
  rollback of all changes performed by the statement. Failure of
  a trigger causes the statement to fail, so trigger failure
  also causes rollback. For nontransactional tables, such
  rollback cannot be done, so although the statement fails, any
  changes performed prior to the point of the error remain in
  effect.

Triggers can contain direct references to tables by name, such as
the trigger named testref shown in this
example:

Изменение триггера

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

Вы также можете переименовать триггер. Для этого используйте запрос ALTER TRIGGER:

ALTER TRIGGER name ON table_name RENAME TO new_name

смотрите в документации

Как создать триггер SQL

Вот составляющие создания триггера для вашей базы данных:

• Тип события триггера
• До или после события
• Воздействие триггера

Типы событий триггера

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

Триггер базы данных допускает также перечисление более одного из этих событий.

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

Триггер BEFORE (до) или AFTER (после)

Триггер может выполняться либо до, либо после события.

Если вы хотите заблокировать событие типа INSERT, вы захотите выполнять действие до (BEFORE). Если вы хотите быть уверенным, что событие действительно произойдет, идеальный вариант — после (AFTER).

Воздействие триггера

Триггер может выполняться либо на строку, либо на оператор. Скажем, вы выполняете один оператор UPDATE, который изменяет 5 строк в таблице.

Если вы укажете в триггере FOR EACH ROW, тогда триггер выполнится 5 раз. Если вы укажете FOR EACH STATEMENT, тогда он выполнится только раз.

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

Примеры

Выполнение функции check_account_update перед любым изменением строк в таблице accounts:

CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();

Изменение определения триггера, чтобы данная функция выполнялась только при указании столбца balance в качестве целевого столбца команды UPDATE:

CREATE OR REPLACE TRIGGER check_update
BEFORE UPDATE OF balance ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_account_update();

В этом примере функция будет выполняться, если значение столбца balance в действительности изменилось:

CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE FUNCTION check_account_update();

Вызов функции, ведущей журнал изменений в accounts, но только если что-то изменилось:

CREATE TRIGGER log_update
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_account_update();

Выполнение для каждой строки функции view_insert_row, которая будет вставлять строки в нижележащие таблицы представления:

CREATE TRIGGER view_insert
INSTEAD OF INSERT ON my_view
FOR EACH ROW
EXECUTE FUNCTION view_insert_row();

Выполнение функции check_transfer_balances_to_zero для каждого оператора, проверяющей, что строки transfer в совокупности дают нулевой баланс:

CREATE TRIGGER transfer_insert
AFTER INSERT ON transfer
REFERENCING NEW TABLE AS inserted
FOR EACH STATEMENT
EXECUTE FUNCTION check_transfer_balances_to_zero();

Выполнение функции check_matching_pairs для каждой строки, проверяющей, что соответствующие пары пунктов изменены синхронно (одним оператором):

CREATE TRIGGER paired_items_update
AFTER UPDATE ON paired_items
REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
FOR EACH ROW
EXECUTE FUNCTION check_matching_pairs();

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

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

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

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

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

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

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

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

CREATE CONSTRAINT TRIGGER — реализованное в расширение стандарта SQL, так же как и указание OR REPLACE.

Создание триггера

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

Синтаксис запроса следующий:

где событие (event) может быть одним из следующих:

Здесь требуется несколько пояснений.

• Вы можете создать (CREATE) или заменить (REPLACE) уже существующий триггер.
• Вы сразу связываете функцию с конкретной таблицей, представлением или внешней таблицей. Код будет исполняться только при наступлении события с этой связанной сущностью.
• Триггеры с опцией INSTEAD OF должны быть помечены опцией FOR EACH ROW и могут быть определены только в представлениях. Триггеры, которые выполняются до (BEFORE) или после события (AFTER) в представлении должны быть помечены как FOR EACH STATEMENT. В есть таблица, которая поможет сориентироваться.

Важные моменты, которые следует помнить

• Чтобы создать триггер, пользователь должен иметь привилегию TRIGGER для таблицы и привилегию EXECUTE для функции.
• Вы можете проверить системный каталог «pg_trigger» на наличие существующей информации о триггерах в базе данных.
• Если вы создадите несколько триггеров для одного и того же объекта и для одного и того же события, они будут срабатывать в алфавитном порядке по имени.

В своем официальном канале Timeweb Cloud собрали комьюнити из специалистов, которые говорят про IT-тренды, делятся полезными инструкциями и даже приглашают к себе работать.

Удаление триггера

Используйте DROP TRIGGER, чтобы удалить триггер PostgreSQL. Синтаксис очень простой:

Например, так вы удалите some_example_of_trigger, связанный с таблицей Example:

DROP TRIGGER some_example_of_trigger ON «Example» ;

PostgreSQL отключить триггер, пользователь должен быть владельцем таблицы.

Можно использовать дополнительные параметры при отключении:

• IF EXISTS — указание на то, что не надо выдавать ошибку, если такого триггера нет.
• CASCADE — автоматически удалять все объекты, которые зависят от триггера, объекты, которые зависят от этих объектов, и так далее.
• RESTRICT — не отключать триггер, если от него зависят другие объекты. Это значение по умолчанию.

Параметры

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

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

Принимает одно из значений: INSERT, UPDATE, DELETE или TRUNCATE; этот параметр определяет событие, при котором будет срабатывать триггер. Несколько событий можно указать, добавив между ними слово OR, если только не запрашиваются переходные отношения.

Для событий UPDATE можно указать список столбцов, используя такую запись:

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

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

Имя (возможно, дополненное схемой) таблицы, представления или сторонней таблицы, для которых предназначен триггер.

Имя (возможно, дополненное схемой) другой таблицы, на которую ссылается ограничение. Оно используется для ограничений внешнего ключа и не рекомендуется для обычного применения. Это указание допускается только для триггеров ограничений.

Время срабатывания триггера по умолчанию. Подробнее возможные варианты описаны в документации . Это указание допускается только для триггеров ограничений.

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

Это предложение указывает, будет ли следующее имя относиться к переходному отношению с образом-до-изменения или к переходному отношению с образом-после-изменения.

Имя (неполное, без схемы), которое будет использоваться в триггере для обращения к этому переходному отношению.

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

Заданная пользователем функция, объявленная как функция без аргументов и возвращающая тип trigger, которая будет вызываться при срабатывании триггера.

В синтаксисе CREATE TRIGGER ключевые слова FUNCTION и PROCEDURE равнозначны, но указываемая триггерная функция должна в любом случае быть функцией, а не процедурой. Ключевое слово PROCEDURE здесь поддерживается по историческим причинам и считается устаревшим.

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

Практика — добавление информации в две таблицы

Давайте рассмотрим пример создания триггера PostgreSQL, который будет добавлять в таблицу информацию о новом сотруднике, если эти данные появились в другой таблице.

Сначала нужно создать обе таблицы:

CREATE TABLE «Employee»(«EmployeeId» INT NOT NULL,»LastName» VARCHAR(20) NOT NULL,»FirstName» VARCHAR(20) NOT NULL,»Title» VARCHAR(30),»ReportsTo» INT,»BirthDate» TIMESTAMP,»HireDate» TIMESTAMP,»Address» VARCHAR(70),»City» VARCHAR(40),»State» VARCHAR(40),»Country» VARCHAR(40),»PostalCode» VARCHAR(10),»Phone» VARCHAR(24),»Fax» VARCHAR(24),»Email» VARCHAR(60),CONSTRAINT «PK_Employee» PRIMARY KEY  («EmployeeId»));

Таблицы готовы, теперь нужно добавить триггерную функцию, чтобы настроить между ними обмен данными по наступлению события. В нашем случае событие — это добавление информации о новом сотруднике в таблицу «Employee».

Как только мы выполним описанный выше INSERT в «Employee», триггер добавит одну новую запись в «Employee_Audit» со следующими данными:

Теперь проверим, что всё работает так, как мы предполагали. Сначала выведем сведения о сотруднике из таблицы «Employee», в которую мы только что вставили данные:

Теперь посмотрим, записались ли нужные данные в таблицу «Employee_Audit»:

Отлично, всё работает!

Пример триггера в PostgreSQL #2

Аккуратное сохранение данных о нахождении сотрудников критично для бизнеса. Данные подобного типа часто непосредственно отражаются на зарплате и, с другой стороны, на заработках компании.

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

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

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

Создание таблицы аудита

create table time_punch_audit (
id serial primary key,
change_time timestamp not null default now(),
change_employee_id int not null references employee(id),
time_punch_id int not null references time_punch(id),
punch_time timestamp not null
);

В эту таблицу записывается:

• Время обновления прохождения.
• Сотрудник, который выполнил обновление.
• ID прохода, который был изменен.
• Время прохода до того, как было сделано обновление.

Прежде чем создавать триггер, сначала нам нужно добавить столбец change_employee_id в таблицу time_punch. Тогда триггер будет знать, какой сотрудник сделал каждое изменение в таблице time_punch.

alter table time_punch
add column change_employee_id int null references employee(id);

После того, как произойдет обновление таблицы time_punch, выполнится этот триггер и запишет OLD (старое) значение времени прохода в нашу таблицу аудита.

create or replace function fn_change_time_punch_audit() returns trigger as $psql$
begin
insert into time_punch_audit (change_time, change_employee_id, time_punch_id, punch_time)
values
(now(), new.change_employee_id, new.id, old.punch_time);
return new;
end;
$psql$ language plpgsql;

create trigger change_time_punch_audit after update on time_punch
for each row execute procedure fn_change_time_punch_audit();

Функция NOW() возвращает текущую дату и время с точки зрения сервера SQL. Если бы это было привязано к настоящему приложению, вы, вероятно, захотели бы передавать точное время, когда пользователь фактически сделал запрос, чтобы избежать расхождения из-за задержки.

Для триггера на обновление объект NEW представляет те значения, которые будут содержаться
в строке при успешном обновлении. Вы можете использовать триггер для «перехвата» вставки или обновления простым присвоением своих собственных значений в объект NEW. Объект OLD содержит значения строки до обновления.

Проверим, работает ли это! Я добавил второго пользователя с именем Daniel, который будет редактором времени прохода Bear.

Я собираюсь выполнить дважды нижеприведенный запрос для имитации 2 редакций, которые увеличивают время на 5 минут.

update time_punch
set punch_time = punch_time + interval ‘5 minute’, change_employee_id = 2
where id = 2;

А вот таблица аудита, отражающая прошлые времена прохода:

Преимущества использования триггеров SQL

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

Разделение бизнес-логики

Размещение критичной бизнес-логики в коде приложения также представляет проблему, когда бизнес-логика обновляется. Если вашим бизнес-требованием являлось умножение входящих номеров на 10, а теперь вы захотели умножать это число на 20, изменение логики в SQL гарантировало бы, что каждые данные, начиная точно с момента развертывания, будут обрабатываться новой логикой.

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

Атомарные транзакции

Естественная атомарность является еще одной желательной особенностью, присущей триггерам. Поскольку событие и триггерная функция являются частью одной атомарной транзакции, вы знаете с абсолютной определенностью, что триггер сработает, если возникнет событие. Они как единое целое в идеальном браке SQL.

Использование триггеров

Это довольно мощный инструмент, у которого много сценариев использования. Вот лишь несколько примеров:

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

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

Если каждый раз сканировать таблицу, выполнять группировку, сортировку и все расчёты (допустим, вычисление среднего значения), то на больших данных быстро станет заметной неэффективность работы — не помогут даже мощные

Чтобы не обрабатывать все данные каждый раз заново, можно использовать — это представления, которые сохраняют результаты в табличной форме. Они позволяют закэшировать данные. Проблема в том, что при каждом обновлении представление пересчитывается целиком. На больших данных это снова может стать проблемой.

Здесь на помощь и приходит триггер. Он позволяет создать по сути тот же Materialized View, только умный. Он не пересчитывает все данные, а обновляет только ту строку, в которую внесли изменения.

Замечания

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

Для удаления триггера применяется команда DROP TRIGGER.

При создании триггера уровня строк для секционированной таблицы такой же триггер будет создан в каждой из её существующих секций; идентичный триггер будет установлен и в каждой секции, создаваемой или присоединяемой позднее. Если в дочерней таблице встретится триггер с конфликтующим именем, возникнет ошибка. Исключение составляет команда CREATE OR REPLACE TRIGGER, которая заменит существующий одноимённый триггер клонированным. При отсоединения секции от родительской таблицы клонированные триггеры в ней удаляется.

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

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

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

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

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

Синтаксис

Триггеры делятся на два типа в зависимости от того, на каком уровне они действуют.

Если триггер помечен опцией FOR EACH ROW, тогда функция вызывается для каждой строки, которая изменяется в результате события. Например, если сделать UPDATE для 100 строк, триггерная функция UPDATE будет вызываться 100 раз, по одному разу для каждой обновлённой строки.

Опция FOR EACH STATEMENT вызовет функцию только один раз для каждого оператора, независимо от количества изменяемых строк.

Дополнительные сообщажения относительно триггеров

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

• Обслуживание триггеров с течением времени.
• Связанная логика триггера.
• Опыт разработчиков.

Обслуживание триггеров с течением времени

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

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

Связанная логика триггера

Триггеры также могут запускать другие триггеры, быстро усложняя результаты казалось бы невинных INSERT или UPDATE. Этот риск также может привести к побочным эффектам кода приложения.

Опыт разработчиков

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

Изначально SQL может показаться неуклюжим и неудобным языком для изучения, поскольку многие из шаблонов, которые вы изучаете для построения запроса, «вывернуты наизнанку» по сравнению с тем, как вы извлекаете данные на процедурном языке.

Я надеюсь, что у вас появится возможность изучить и реализовать одну из самых увлекательных и интригующих функций SQL!

Простые примеры

Чтобы разобраться с синтаксисом, посмотрим на примеры триггеров PostgreSQL.

Например, здесь вы говорите движку, что нужно выполнять функцию check_account_update() каждый раз до обновления таблицы accounts:

CREATE TRIGGER check_update    BEFORE UPDATE ON accounts    FOR EACH ROW    EXECUTE FUNCTION check_account_update();

В этом примере вы устанавливаете дополнительное условие. Функция должна выполняться только в том случае, если обновляется столбец balance в таблице accounts.

CREATE OR REPLACE TRIGGER check_update    BEFORE UPDATE OF balance ON accounts    FOR EACH ROW    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();

Ещё один пример — с INSTEAD OF. Функция view_insert_row() выполняется для каждой строки, чтобы вставить строки в таблицы, лежащие в основе представления:

CREATE TRIGGER view_insert    INSTEAD OF INSERT ON my_view    FOR EACH ROW    EXECUTE FUNCTION view_insert_row();

Триггер на удаление в PostgreSQL можно добавить к транзакциям, удаляющим записи:

CREATE TRIGGER example_delete_trigger  AFTER DELETE   ON my_view  FOR EACH ROW EXECUTE PROCEDURE aft_delete();

Триггер (базы данных)

Текущая версия страницы пока не проверялась опытными участниками и может значительно отличаться от версии, проверенной 6 марта 2017 года; проверки требуют 8 правок.

Три́ггер (англ. ) — хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определённом столбце заданной таблицы реляционной базы данных.

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

Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события; например, до добавления записи) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись (конечно, при условии, что событие — не удаление записи). Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, на которой «висит» триггер, и т. п.).

Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

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

Пример (Oracle Database):

/* Триггер на уровне таблицы */

‘table «district» has changed’

В этом случае для отличия табличных триггеров от строчных вводится дополнительные ключевые слова при описании строчных триггеров. В Oracle это словосочетание FOR EACH ROW.

/* Триггер на уровне строки */

‘one row in table «district» has changed’

Пример триггера #1

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

Настройка схемы базы данных

Структура схемы предполагает каждый вход и выход отдельными событиями. Каждое событие — это строка в таблице time_punch. Как альтернативу вы можете также сделать каждую «рабочую смену» сотрудника событием и хранить время как входа, так и выхода в одной строке.

В следующей статье я глубже погружусь в разработку схемы базы данных.

Для нашего примера я уже разработал схему таблиц. Нижеприведенный код создает таблицы employee и time_punch и вставляет некоторые данные по времени прохода для нового сотрудника Bear.

Bear зашел в 10:00 и вышел в 11:30 (длинный рабочий день). Давайте напишем запрос SQL для вычисления рабочего времени Bear.

Остановитесь и подумайте, как бы вы решили эту задачу при данной схеме и с помощью одного SQL.

Использование SQL для вычисления рабочего времени

Решение, которое я предлагаю, ищет на каждый «выход» соответствующий ему «вход».

select tp1.punch_time — tp2.punch_time as time_worked
from time_punch tp1
join time_punch tp2
on tp2.id = (
select tps.id
from time_punch tps
where tps.id < tp1.id
and tps.employee_id = tp1.employee_id
and not tps.is_out_punch
order by tps.id desc limit 1
)
where tp1.employee_id = 1 and tp1.is_out_punch;

time_worked
2
————-
3
01:30:00 (1 row)

В этом запросе я выбираю все выходы, затем я соединяю их с наиболее близким «входом». Беру разность временных меток и получаю количество часов, которое отработал Bear в каждой смене!

Одна из проблем в этой схеме состоит в том, что возможно вставить несколько «входов» или «выходов» подряд. С созданным запросом это приведет к неоднозначности, которая может привести к неточным расчетам и зарплате сотрудников — тбольше или меньше, чем они должны были бы получить.

Сохранение целостности данных

Нам требуется то, что не позволит нарушить шаблон вход/выход. К сожалению, ограничения check только отслеживают вставляемую или обновляемую строку и не могут учитывать данные из других строк.

Это идеальная ситуация для использования триггера баз данных!

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

Триггерная функция создается как обычная функция PostgreSQL за тем исключением, что возвращает триггер.

create or replace function fn_check_time_punch() returns trigger as $psql$
begin
if new.is_out_punch = (
select tps.is_out_punch
from time_punch tps
where tps.employee_id = new.employee_id
order by tps.id desc limit 1
) then
return null;
end if;
return new;
end;
$psql$ language plpgsql;

Ключевое слово new представляет значения вставляемой строки. Это также объект, который вы можете вернуть, чтобы позволить продолжиться вставке. Напротив, возвращение null остановит вставку.

Этот запрос сначала находит в time_punch предыдущее значение и гарантирует, что это значение входа/выхода не совпадает с вставляемым значением. Если значения совпадают, то триггер возвращает null, и time_punch не записывается. В противном случае, триггер возвращает new и оператор insert продолжается.

Теперь мы привяжем функцию в качестве триггера к таблице time_punch. BEFORE здесь ключевой момент. Если мы выполним этот триггер как триггер AFTER, он будет выполнен слишком поздно, чтобы остановить вставку.

create trigger check_time_punch before insert on time_punch
for each row execute procedure fn_check_time_punch();

Давайте попробуем вставить еще один «выход»:

insert into time_punch (employee_id, is_out_punch, punch_time)
values
(1, true, ‘2020-01-01 13:00:00’);

Output: INSERT 0 0

Как можно видеть по выводу, триггер предотвратил вставку двух последовательных выходов для одного и того же сотрудника.

Можно также вызвать исключение из триггера с тем, чтобы ваше приложение (или лицо, выполняющее запрос SQL) получило уведомление об отказе вместо 0 как числа вставленных строк.