Триггеры PostgreSQL

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

Ниже приведены некоторые важные моменты о триггерах PostgreSQL:

• Триггеры PostgreSQL могут срабатывать в следующих ситуациях:

  • Перед выполнением операции (до проверки ограничений и попытки вставки, обновления или удаления).
  • После выполнения операции (после проверки ограничений и завершения вставки, обновления или удаления).
  • Операции INSTEAD OF (при вставке, обновлении или удалении в представлении).

• Атрибут FOR EACH ROW триггера является необязательным. Если он выбран, он вызывается один раз для каждой строки, изменённой операцией; наоборот, если выбран FOR EACH STATEMENT, триггер выполняется один раз для каждого оператора, независимо от того, сколько строк изменено.

• Предложение WHEN и операции триггера могут обращаться к элементам каждой строки при ссылке на NEW.column-name и OLD.column-name для операций вставки, удаления или обновления. Здесь column-name — имя столбца в таблице, с которой связан триггер.

• Если предложение WHEN существует, оператор PostgreSQL выполняется только для строки, для которой предложение WHEN истинно; если предложения WHEN нет, оператор PostgreSQL выполняется для каждой строки.

• Ключевое слово BEFORE или AFTER определяет, когда выполняется действие триггера, решая, выполняется ли действие триггера до или после вставки, изменения или удаления связанной строки.

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

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

Синтаксис

Базовый синтаксис для создания триггера выглядит следующим образом:

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- 触发器逻辑....
];

Здесь event_name может быть операцией с базой данных, такой как INSERT, DELETE и UPDATE для указанной таблицы table_name. После имени таблицы вы можете дополнительно указать FOR EACH ROW.

Ниже приведён синтаксис для создания триггера для операций UPDATE на одном или нескольких указанных столбцах таблицы:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- 触发器逻辑....
];

Примеры

Предположим ситуацию, когда мы хотим вести аудиторский след для каждой записи, вставленной во вновь созданную таблицу COMPANY (если она уже существует, удалите и создайте её заново):

exampledb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Чтобы вести аудиторский след, мы создадим новую таблицу с именем AUDIT. Каждый раз, когда в таблицу COMPANY добавляется новая запись, в неё будет вставляться сообщение журнала:

exampledb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Здесь ID — это ID записи AUDIT, EMP_ID — это ID из таблицы COMPANY, а DATE будет хранить временную метку создания записи в COMPANY. Итак, теперь давайте создадим триггер на таблице COMPANY следующим образом:

exampledb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

auditlogfunc() — это функция PostgreSQL, определение которой выглядит следующим образом:

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Теперь давайте начнём вставлять данные в таблицу COMPANY:

exampledb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

На данный момент в таблицу COMPANY была вставлена запись:

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

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

Список триггеров

Вы можете вывести список всех триггеров в текущей базе данных из таблицы pg_trigger:

exampledb=# SELECT * FROM pg_trigger;

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

exampledb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

Полученный результат выглядит следующим образом:

 tgname
-----------------
 example_trigger
(1 row)

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

Базовый синтаксис удаления триггера выглядит следующим образом:

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

Команда для удаления триггера example_trigger в таблице company выше:

drop trigger example_trigger on company;

Другие расширения