SQL-Ex blog

Что такое триггер базы данных?

Триггер представляет собой блок кода, который автоматически выполняется после исполнения некоторой операции с таблицей или представлением базы данных, точнее, после операции Insert, Update, Delete. Например, в приложении банка триггер может использоваться для вставки данных в таблицу истории/аудита для всех имеющих место исходных транзакций.

Некоторые ключевые моменты:

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


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


• Еще одним недостатком триггеров является сложность отслеживания и понимания их логики.

Имеется два основных типа триггеров: триггеры уровня строки и уровня оператора.

Триггер уровня строки

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

Триггер уровня оператора

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

Триггеры в PostgreSQL

Концептуально триггер в PostgreSQL подобен триггеру в SQL Server, однако имеется ряд ключевых отличий, перечисленных ниже:

• Триггеры в PostgreSQL поддерживают операции truncate.


• PostgreSQL не поддерживает триггеры, не имеющие связанной триггерной функции.

Триггеры в PostgreSQL поддерживают следующие операции DML:

• Create trigger - используется для создания триггера


• Drop trigger - используется для удаления триггера


• Alter trigger - используется для изменения имени существующего триггера


• Disable trigger - используется для отключения заданного или всех триггеров, связанных с таблицей


• Enable trigger - используется для включения конкретного или всех триггеров, связанных с таблицей

Базовый синтаксис триггера в PostgreSQL:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} { событие }
ON table_name
[FOR [EACH] { ROW | STATEMENT }]
EXECUTE PROCEDURE trigger_function

Давайте рассмотрим этот синтаксис.

Для начала триггеру дается имя - trigger_name. За ним следует время срабатывания триггера, которым может быть либо before (до), либо after (после), в зависимости от операции, которая должна быть выполнена на целевой таблице. Затем указывается одно из следующих событий - insert, update, delete, truncate. Затем указывается имя таблицы с последующим типом триггера, которым является row (на строку) или statement (на оператор). Наконец, указывается связанная с триггером функция - trigger_function:

Базовый синтаксис триггерной функции PostgreSQL

CREATE FUNCTION trigger_function() 

   RETURNS TRIGGER 

   LANGUAGE PLPGSQL

AS $$

BEGIN

   -- логика триггера

END;

$$

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

Пример триггера уровня строки

Сценарий: Имеется две таблицы stocks и stock_audits; для каждой строки данных, вставленных в таблицу stocks выполняется триггер stocks_trigger и вставляет одну строку данных в другую таблицу stock_audits.

CREATE TRIGGER stocks_trigger 

AFTER INSERT ON public."Stocks"

FOR EACH ROW 

EXECUTE PROCEDURE stock_auditfunc();

CREATE OR REPLACE FUNCTION stock_auditfunc() RETURNS TRIGGER AS $my_table$

   BEGIN

      INSERT INTO stocks_audit(stock_id, entry_date) VALUES (new.ID, current_timestamp);

      RETURN NEW;

   END;

$my_table$ LANGUAGE plpgsql;

Таблицы до вставки:




Таблица после вставки и выполнения триггера:

INSERT INTO public."Stocks" 

VALUES (1,3000,'TCS');

Замечание: Для массовой операции, например, когда сразу обрабатывается 100 строк, триггер уровня строки выполняется 100 раз.

Триггер уровня оператора

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

CREATE TRIGGER stocks_trigger 

AFTER INSERT ON public."Stocks"

FOR EACH STATEMENT

EXECUTE PROCEDURE stock_auditfunc();

Доступ к триггерам PostgreSQL с помощью PgAdmin и консольного приложения PSQL

С триггерами в PostgreSQL можно эффективно работать либо с помощью PgAdmin, либо посредством psql-терминала, как показывается ниже.

Доступ к триггерам посредством PgAdmin:

Триггеры имеют отношение к таблицам и перечисляются в ветке каждой таблицы, как показано ниже,
Servers->User Server->Databases->User Database->Schemas->User Schema->Tables-> User Tables->Triggers

Доступ к триггерам посредством psql:

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

Шаг 1: Подключиться к требуемой базе данных

\c [имя_базы_данных]

Шаг 2: Получить список триггеров

\dS [имя_таблицы]