Триггер

Это объект, который автоматически выполняется при наступлении каких-либо событий в таблице или представлении.

Что позволяет делать:

1. Контролировать данные
2. Вести аудит изменений

Общий вид:

create trigger имя_тригерра вид_триг тип_триг on ИДТ
for each row|statement
execute function ИНДФ();

Виды триггеров

1. before — триггер выполняется до модификации данных.
2. after — триггер будет выполняться после модификации данных.
3. instead of — заменяет операцию (используется только с view (представлениями)).

Что можно делать в триггере?

• Все действия которые можно делать над таблицей, можно делать в триггере

Нельзя использовать:

1. Команды администрирования.
2. Журнал транзакции (это не команды транзакции).

Типы триггера

1. update
2. insert
3. delete

При помощи типа триггера указывается, на какую команду будет запускаться триггер. Типов триггера можно объявить несколько для одного триггера.

Общий вид: create trigger ИНДФ Вид_тригера Тип or Тип or Тип

create trigger primer before insert or delete
create trigger primer before insert
create trigger primer before insert or update or delete

Как работает триггер

При срабатывании триггер создаёт 2 системно-временные таблицы с постоянным названием new и old

Структура таблиц new и old идентична таблице для которой создан триггер

Каждый триггер создаёт свой комплект таблиц new и old

1 триггер не может обращаться в чужие new и old таблицы

В таблицу new будут заноситься данные которые пользователь пытается добавить или обновить

В таблицу old будут заноситься данные, которые пользователь пытается удалить или обновить.

Если на insert: В таблицу new будут занесены данные которые пользователь пытается внести, то таблица old будет пустой

• Если проверка триггера проходит успешно, из таблицы new данные перенесется в исходную таблицу
• Если проверка триггера не проходит успешно (выпадает ошибка), то данные из таблицы new будут удалены

Если на delete: В таблицу old будут перенесены все строки которые пользователь пытается удалить, то таблица new будет пустой

• Если проверка триггера проходит успешно, то данные из таблицы old удаляются
• Если проверка триггера не проходит успешно, то из old будут перенесены обратно в исходную таблицу

Если на update: В таблицу new будут заноситься все новые значения которые пользователь хочет заменить в таблицу old будут заноситься Старые значения исходной таблицы

• Если проверка триггера проходит успешно то данные из old удаляются, а из new переносится в исходную таблицу
• Если проверка триггера не проходит успешно, то данные из old возвращаются в исходную таблицу а данные из new удаляются

for each

for each row|statement row - проверяет каждую строку отдельно statement - проверка блоком(все строки за 1 раз)

Общий вид:

create function ИНДФ вид_триг тип_триг on ИДТ
for each row|statement execute ИНДФ-Ф()

Триггеры работают вместе с подпрограммой (функцией), то есть условия проверки пишутся в function. Для того чтобы функция могла работать с триггером, в функции всегда нужно указывать returns trigger.

Общий вид:

create function ИНДФ()
returns trigger
as $$
Begin
	-тело
End; $$ language plpgsql;

Внутри тела триггера можно использовать специальные обозначения:

1. tg_OP - операции (insert, update, delete)
2. tg_name - Имя триггера
3. tg_when - момент срабатывания
4. tg_level - указывается ограничения на проверку (row, statement)
5. tg_table_name - указания имени таблицы
6. tg_table_schema — указание имени схемы.
7. tg_avg — массив аргументов (который вносится при срабатывании триггера).

Пример:

raise notice 'Сработал триггер % на таблице % при операции %', tg_name, tg_table_name, tg_op;

В теле функции можно указывать return

1. return new - используется с insert|update строка из таблицы new которая будет выставлена или обновлена
2. return old - используется с delete|update строка из таблицы old будет удалена
3. return null - отмена действия

Пример:

create or replace function Дефолт_имя_триг()
returns trigger as $$
Begin
	if new.фамилия is null then
		new.фамилия = 'Джон Доу';
	end if;
	return new;
End; $$ language plpgsql;

create trigger деф_знач before insert on s
for each row execute function деф_знач()

insert into s
values ('s1', null, 100, 'tomsk')

create or replace function Запрет_удаления()
returns trigger as $$
Begin
	if Должность = 'Директор' then
	raise exception 'ты кто такой что бы меня удалять';
	end if;
	return old;
End; $$ language plpgsql;

create trigger Запрет_Удаление before delete on Должн
for each row execute function Запрет_удаления()

delete from Должн

Условие при создании триггера

При создании триггера можно написать условия для запуска функции делается при помощи when

create trigger ИНДФ ВИД ТИП on ИДТ
for each row
when (условие)
execute function Название_функции

Если условие when не выполняется, то функция не будет запущена.

Пример:

create trigger проверка_на_0 before insert or update on ИДТ
for each row when (кол-во = 0) execute function Падение_в_ошибку();

create function Падение_в_ошибку()
returns trigger as $$
Begin
	raise exception 'где то накосячил...';
End; $$ language plpgsql;

Конструкции проверки if в триггерных функции

1)if ... then

Begin
	If условие then
		Действие
	End if;
End;

2)if ... then ... else

Begin
	if условие then
		Действие
	else
		Действие
	end if;
End;

3)if ... elsif ... else

Begin
	if условие then
		Действие
	elsif условие then
		Действие
	...
	else
		Действие
	end if;
End;

4)Вложенные условие

Begin
if условие then
	if условие then
		Действие
	end if;
end if;
End;

5)case Позволяет сделать конструкторное условие аналог if-else для запроса(select). Только в теле функции

Begin
	ИДС = case
	when условия then
		Действие
	...
	else
		Действие
	end;
End;

Пример:

Begin
	Статус = case
		when возраст < 30 then 'Junior'
		when возраст > 40 then 'Senior'
		else 'Middle'
	end;
end;

when vs if

When:

• позволяет поставить условие до запуска функции
• минус, позволяет сделать простые условия

if:

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

instead of

Работает с view. Представление может передать данные в исходную таблицу только при соблюдении некоторых условий, одно из них использовать 2 и более таблицы в view

instead of позволяет заменять логику работы представления, логику работы триггера

Особенности:

1. Работает только с view
2. Всегда for each row
3. Прописывается всегда return new(для insert или update) и return old(для delete или update)

Общий вид:

create trigger ИНДФ instead of insert|update|delete on ИНДФ_представления
for each row execute function ИНДФ-Ф()

Пример:

create view gr632_v_test as select
s.фамилия, s.номер_поставщика, spj.номер_детали, spj.номер_изделия, spj.клочество
from s inner join spj on
s.номер_поставщика = spj.номер_поставщика

insert into gr632_v_test
values ('POOO', 's20', 'p1', 'j1', 100)
-- в данный момент не работает

create or replace function gr632_v_f()
returns trigger as $$
Begin
	if new.фамилия is not null then
		insert into s values (new.номер_поставщика, new.фамилия);
		return new;
	elseif номер_детали is not null then
		insert into spj values (new.номер_поставщика, new.номер_детали, new.номер_изделия, new.количество);
		return new;
	end if;
End;$$ language plpgsql;

create or replace trigger gr632_v_t insead of insert on gr632_v_test
for each row execute function gr632_v_f()

insert into gr632_v_test
values ('pooo', 's20', 'p1', 'j1',100)