PostgreSQL - ТРИГГЕРЫ

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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 - это ID записи AUDIT, а EMP_ID - это ID, который будет взят из таблицы COMPANY, а DATE сохранит временную метку, когда запись будет создана в таблице COMPANY. Итак, теперь давайте создадим триггер для таблицы COMPANY следующим образом:

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

Где auditlogfunc () - это PostgreSQL procedure и имеет следующее определение -

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, которую можно использовать для удаления существующего триггера:

testdb=# DROP TRIGGER trigger_name;