Стасышина
Татьяна Леонидовна
pk@nstu.ru, +7 (383) 319 59 99 — приёмная комиссия

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;

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

Имя
Тип
Описание
NEWRECORDНовые  значения полей записи базы данных, созданной командой  INSERT или  обновленной командой UPDATE, при срабатывании триггера уровня записи (ROW). Переменная используется для модификации новых записей. Внимание !!! Переменная NEW доступна только при операциях INSERT и UPDATE. Поля записи NEW могут быть изменены триггером.
OLDRECORDСтарые  значения полей записи базы данных, содержавшиеся  в записи  перед выполнением команды  DELETE или UPDATE при срабатывании триггера уровня записи (ROW) Внимание !!! Переменная OLD доступна только при операциях DELETE и UPDATE. Поля записи OLD  можно использовать только для чтения, изменять нельзя.
TG_NAMEnameИмя сработавшего триггера.
TG_WHENtextСтрока BEFORE или AFTER в зависимости от момента срабатывания триггера, указанного в определении (до или после операции).
TG_LEVELtextСтрока ROW или STATEMENT в зависимости от уровня триггера, указанного в определении.
TG_OPtextСтрока INSERT, UPDATE или DELETE в зависимости от операции,    вызвавшей срабатывание триггера.
TG_RELIDoidИдентификатор объекта таблицы, в которой сработал триггер.
TG_RELNAMEnamename  Имя таблицы, в которой сработал триггер.

К отдельным полям записей 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       

Наверх