7. Создание триггеров в PostgreSql

Триггер определяет операцию, которая должна выполняться при наступлении некоторого события в базе данных. Триггеры срабатывают при выполнении с таблицей команды SQL INSERT, UPDATE или DELETE.
В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер.

Синтаксис определения триггера

CREATE TRIGGER триггер
BEFORE | AFTER } { событие [ OR событие ] } ON таблица
FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE функция ( аргументы )

Ниже приводятся краткие описания компонентов этого определения.

•       CREATE TRIGGER триггер. В аргументе триггер указывается произвольное имя создаваемого триггера. Имя может совпадать с именем триггера, уже существующего в базе данных   при условии, что этот триггер установлен для другой таблицы. Кроме того, по аналогии с большинством других несистемных объектов баз данных, имя триггера (в сочетании с таблицей, для которой он устанавливается) должно быть уникальным лишь в контексте базы данных, в которой он создается
•       { BEFORE | AFTER }. Ключевое слово BEFORE означает, что функция должна выполняться перед попыткой выполнения операции, включая все встроенные проверки ограничений данных, реализуемые при выполнении команд INSERT и DELETE. Ключевое слово AFTER означает, что функция вызывается после завершения операции, приводящей в действие триггер.
•       { событие [ OR событие ... ] }. События SQL, поддерживаемые в PostgreSQL: INSERT, UPDATE или DELETE. При перечислении нескольких событий в качестве разделителя используется ключевое слово OR.
•       ON таблица. Имя таблицы, модификация которой заданным событием приводит к срабатыванию триггера.
•       FOR EACH { ROW | STATEMENT }. Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Ключевое слово ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT.
•       EXECUTE PROCEDURE функция ( аргументы ). Имя вызываемой функции с аргументами. На практике аргументы при вызове триггерных функций не используются.

Синтаксис определения триггерной функции

CREATE FUNCTION функция () RETURNS trigger AS '
DECLARE
объявления ;
BEGIN
команды;
END; '
LANGUAGE plpgsql;

В триггерных функциях используются специальные переменные, содержащие информацию о сработавшем триггере. С помощью этих переменных триггерная функция работает с данными таблиц.
Ниже перечислены некоторые специальные переменные, доступные в триггерных функциях

Имя	Тип	Описание
NEW	RECORD	Новые значения полей записи базы данных, созданной командой INSERT или обновленной командой UPDATE, при срабатывании триггера уровня записи (ROW). Переменная используется для модификации новых записей. Внимание !!! Переменная NEW доступна только при операциях INSERT и UPDATE. Поля записи NEW могут быть изменены триггером.
OLD	RECORD	Старые значения полей записи базы данных, содержавшиеся в записи перед выполнением команды DELETE или UPDATE при срабатывании триггера уровня записи (ROW) Внимание !!! Переменная OLD доступна только при операциях DELETE и UPDATE. Поля записи OLD можно использовать только для чтения, изменять нельзя.
TG_NAME	name	Имя сработавшего триггера.
TG_WHEN	text	Строка BEFORE или AFTER в зависимости от момента срабатывания триггера, указанного в определении (до или после операции).
TG_LEVEL	text	Строка ROW или STATEMENT в зависимости от уровня триггера, указанного в определении.
TG_OP	text	Строка INSERT, UPDATE или DELETE в зависимости от операции, вызвавшей срабатывание триггера.
TG_RELID	oid	Идентификатор объекта таблицы, в которой сработал триггер.
TG_RELNAME	name	name Имя таблицы, в которой сработал триггер.

К отдельным полям записей NEW и OLD в триггерных процедурах обращаются следующим образом: NEW.names , OLD.rg.

Примеры создания триггеров

Пример 1. Триггер выполняется перед удалением записи из таблицы поставщиков s. Триггер проверяет наличие в таблице поставок spj записей, относящихся к удаляемому поставщику, и, если такие записи есть, удаляет их.

-- Создание триггерной функции
CREATE FUNCTION trigger_s_before_del () RETURNS trigger AS '
BEGIN
if (select count(*) from spj a where trim(a.ns)=trim(OLD.ns))>0
then delete from spj where trim(spj.ns)=trim(OLD.ns);
end if;
return OLD;
END;
' LANGUAGE plpgsql;

-- Создание триггера
CREATE TRIGGER tr_s_del_befor
BEFORE DELETE ON s FOR EACH ROW
EXECUTE PROCEDURE trigger_s_before_del();

--Проверка работы триггера
Delete from s where ns=’S2’;

Пример 2. Создание триггера-генератора для таблицы поставщиков s.

Триггер выполняется перед вставкой новой записи в таблицу поставщиков s. Триггер проверяет значения, которые должна содержать новая запись (record NEW) и может их изменить:

eсли не указан номер поставщика – он генерируется по схеме – S+ уникальный номер из последовательности;
eсли не указано имя поставщика – оно генерируется по схеме – Postawchik_ + уникальный номер из последовательности;
eсли не указан город – ставится значение по умолчанию – “Novosibirsk” ;
если не указан рейтинг или рейтинг <=0 – устанавливается рейтинг = 10 для поставщиков из Novosibirskа и 0 для всех остальных.

-- Создание последовательности
CREATE SEQUENCE s_seq INCREMENT BY 1 START WITH 25;

-- Создание триггерной функции
-- в этой функции вызывается перегружаемая функция nvl, ее определение здесь
CREATE FUNCTION trigger_s_before_lns () RETURNS trigger AS '
BEGIN
NEW.ns=nvl(NEW.ns,'S'||trim(to_char(nextval('s_seq'),'99999')));
NEW.names=nvl(NEW.names,'Postawchik_'||trim(to_char(currval('s_seq'),'99999')));
NEW.town = nvl(NEW. town, 'Novosibirsk' );
if (nvl(NEW.rg,0)<=0) then
       If NEW.town= 'Novosibirsk'   then NEW.rg=10;
      else NEW.rg=0;
      end if;
end if;
return NEW;
END;
' LANGUAGE plpgsql;

-- Создание триггера
CREATE TRIGGER s_bi
BEFORE INSERT ON s FOR EACH ROW
EXECUTE PROCEDURE trigger_s_before_lns ()

--Проверка работы триггера
insert into s values(null,null,null,null);
insert into s values(null,null,null,null);
insert into s values(null,null,null,null);
insert into s values(null,'Ivanov',null,null);
insert into s values(null,'Sidorov',50,null);
insert into s values(null,'Petrov',null,'Moskva');

--Результат

S25	Postawchik_25	10	Novosibirsk
S26	Postawchik_26	10	Novosibirsk
S27	Postawchik_27	10	Novosibirsk
S28	Petrov	0	Moskva
S29	Ivanov	10	Novosibirsk
S30	Sidorov	50	Novosibirsk