Триггер определяет операцию, которая должна выполняться при наступлении некоторого события в базе данных. Триггеры срабатывают при выполнении с таблицей команды SQL INSERT, UPDATE или DELETE. Синтаксис определения триггера Ниже приводятся краткие описания компонентов этого определения. CREATE FUNCTION функция () RETURNS trigger AS ' В триггерных функциях используются специальные переменные, содержащие информацию о сработавшем триггере. С помощью этих переменных триггерная функция работает с данными таблиц.
В PostgreSQL триггеры создаются на основе существующих функции, т.е. сначала командой CREATE FUNCTION определяется триггерная функция, затем на ее основе командой CREATE TRIGGER определяется собственно триггер.
CREATE TRIGGER триггер
BEFORE | AFTER } { событие [ OR событие ] } ON таблица
FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE функция ( аргументы )
• { BEFORE | AFTER }. Ключевое слово BEFORE означает, что функция должна выполняться перед попыткой выполнения операции, включая все встроенные проверки ограничений данных, реализуемые при выполнении команд INSERT и DELETE. Ключевое слово AFTER означает, что функция вызывается после завершения операции, приводящей в действие триггер.
• { событие [ OR событие ... ] }. События SQL, поддерживаемые в PostgreSQL: INSERT, UPDATE или DELETE. При перечислении нескольких событий в качестве разделителя используется ключевое слово OR.
• ON таблица. Имя таблицы, модификация которой заданным событием приводит к срабатыванию триггера.
• FOR EACH { ROW | STATEMENT }. Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного события. Ключевое слово ROW означает, что функция вызывается для каждой модифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT.
• EXECUTE PROCEDURE функция ( аргументы ). Имя вызываемой функции с аргументами. На практике аргументы при вызове триггерных функций не используются.
DECLARE
объявления ;
BEGIN
команды;
END; '
LANGUAGE plpgsql;
Ниже перечислены некоторые специальные переменные, доступные в триггерных функциях
К отдельным полям записей NEW и OLD в триггерных процедурах обращаются следующим образом: NEW.names , OLD.rg.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 Имя таблицы, в которой сработал триггер.
Пример 1. Триггер выполняется перед удалением записи из таблицы поставщиков s. Триггер проверяет наличие в таблице поставок spj записей, относящихся к удаляемому поставщику, и, если такие записи есть, удаляет их. -- Создание триггерной функции --Проверка работы триггера Пример 2. Создание триггера-генератора для таблицы поставщиков s. Триггер выполняется перед вставкой новой записи в таблицу поставщиков s. Триггер проверяет значения, которые должна содержать новая запись (record NEW) и может их изменить: -- Создание последовательности -- Создание триггерной функции -- Создание триггераПримеры создания триггеров
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’;
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
Novosibirsk
S26
Postawchik_26
Novosibirsk
S27
Postawchik_27
Novosibirsk
S28
Petrov
Moskva
S29
Ivanov
Novosibirsk
S30
Sidorov
Novosibirsk