PostgreSQL - Триггеры
Триггеры PostgreSQL – это функции обратного вызова базы данных, которые автоматически выполняются /запускаются, как только происходит определенное событие.
О PostgreSQL триггерах важно знать следующее:
- PostgreSQL триггер может быть указан для срабатывания:
- Перед попыткой выполнения операции над строкой (перед проверкой ограничений или попыткой выполнения INSERT, UPDATE или DELETE);
- После завершения операции (после проверки ограничений и завершения операций INSERT, UPDATE, DELETE)
- Вместо операций (в случае вставки, обновления или удаления в представлении)
- Триггер, для которого указано FOR EACH ROW, вызывается один раз для каждой строки, которую изменяет операция. Напротив, триггер, отмеченный FOR EACH STATEMENT, выполняется только один раз для любой данной операции, независимо от того, сколько строк она изменяет;
- Предложение WHEN, как и триггерные действия, могут обращаться к элементам вставляемой, удаляемой или обновляемой строки с использованием ссылок в форме NEW.имя-столбца и OLD.имя-столбца, где имя-столбца — это имя столбца из таблицы, с которой связан триггер;
- Если есть предложение WHEN, указанные операторы PostgreSQL выполняются только для тех строк, для которых условие WHEN истинно. Если предложение WHEN не указано, операторы PostgreSQL выполняются для всех строк;
- Если для одного и того же события определено несколько триггеров одного типа, они будут срабатывать в алфавитном порядке;
- Ключевые слова BEFORE, AFTER или INSTEAD OF определяют, когда сработают триггеры, относящиеся к внесению, модификации или удалению соответствующей строки;
- Триггеры автоматически удаляются при удалении таблицы, с которой они связаны;
- Таблица, которую нужно изменить, должна существовать в той же базе данных, что и таблица или представление, к которому прикреплен триггер, и нужно использовать только tablename, а не database.tablename;
- Опция CONSTRAINT, если она указана, создает триггер ограничения. Это то же самое, что и обычный триггер, за исключением того, что время срабатывания триггера можно настроить с помощью SET CONSTRAINTS. Ожидается, что триггеры ограничений вызовут исключение, когда ограничения, которые они реализуют, нарушаются.
Синтаксис
Базовый синтаксис для создания триггера выглядит следующим образом:
CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- Логика триггера записывается здесь.... ];
В данном случае event_name может быть INSERT, DELETE, UPDATE и TRUNCATE операции над указанной таблицей. По желанию Вы можете указать 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, имеющая следующее определение:
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 );
Появится следующая запись:
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;
Если Вы хотите пролистать все триггеры для определенной таблицы, используйте 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 ;
