Учебники

PostgreSQL — ТРИГГЕРС

Апрель 4, 2019

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

Ниже приведены важные моменты, касающиеся триггеров PostgreSQL:

  • Триггер PostgreSQL может быть указан для запуска

    • Перед попыткой операции над строкой (перед проверкой ограничений и попыткой INSERT, UPDATE или DELETE)

    • После завершения операции (после проверки ограничений и завершения INSERT, UPDATE или DELETE)

    • Вместо операции (в случае вставки, обновления или удаления в представлении)

  • Триггер, помеченный FOR EACH ROW, вызывается один раз для каждой строки, которую изменяет операция. Напротив, триггер, помеченный FOR EACH STATEMENT, выполняется только один раз для любой данной операции, независимо от того, сколько строк он изменяет.

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

  • Если указано предложение WHEN, указанные операторы PostgreSQL выполняются только для тех строк, для которых предложение WHEN является истинным. Если предложение WHEN не указано, операторы PostgreSQL выполняются для всех строк.

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

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

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

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

  • Опция CONSTRAINT, когда она указана, создает триггер ограничения . Это то же самое, что и обычный триггер, за исключением того, что время срабатывания триггера можно настроить с помощью SET CONSTRAINTS. Ожидается, что триггеры ограничения вызовут исключение, когда реализуемые ими ограничения нарушены.

Триггер PostgreSQL может быть указан для запуска

Перед попыткой операции над строкой (перед проверкой ограничений и попыткой INSERT, UPDATE или DELETE)

После завершения операции (после проверки ограничений и завершения INSERT, UPDATE или DELETE)

Вместо операции (в случае вставки, обновления или удаления в представлении)

Триггер, помеченный FOR EACH ROW, вызывается один раз для каждой строки, которую изменяет операция. Напротив, триггер, помеченный FOR EACH STATEMENT, выполняется только один раз для любой данной операции, независимо от того, сколько строк он изменяет.

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

Если указано предложение WHEN, указанные операторы PostgreSQL выполняются только для тех строк, для которых предложение WHEN является истинным. Если предложение WHEN не указано, операторы PostgreSQL выполняются для всех строк.

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

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

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

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

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

Синтаксис

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

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

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

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

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];

пример

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

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

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

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

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

testdb=# 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, что должно привести к созданию записи журнала аудита в таблице AUDIT. Итак, давайте создадим одну запись в таблице COMPANY следующим образом: 

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

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

 id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000

Одновременно в таблице AUDIT будет создана одна запись. Эта запись является результатом триггера, который мы создали в операции INSERT для таблицы COMPANY. Точно так же вы можете создавать свои триггеры для операций UPDATE и DELETE в соответствии с вашими требованиями. 

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

Листинг ТРИГГЕРС

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

testdb=# SELECT * FROM pg_trigger;

Приведенный выше оператор PostgreSQL перечислит все триггеры.

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

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

Приведенный выше оператор PostgreSQL также перечислит только одну запись следующим образом: 

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

Отбрасывание ТРИГГЕРС

Ниже приведена команда DROP, с помощью которой можно удалить существующий триггер:

91
Share: