Доброго здоровья читателям моего канала programmer's notes. Поддерживаем мой канал.
В продолжении статей о языке PLpgSQL в PostgreSQL. Предыдущая статья этой серии здесь.
Понятие триггера
Триггер это специальный объект, который срабатывает автоматически при наступлении в базе данных некоторого события. При создании триггера указывается когда и при каком событии он должен сработать и какую хранимую функцию он должен запустить при наступлении события.
Все триггеры в PostgreSQL можно разделить на две большие группы:
1. Триггеры, срабатывающие на изменения данных. Их еще называют DML-триггеры. Эти триггеры привязаны к конкретной таблице и запускаются при выполнении операций delete, insert, update.
2. Триггеры, которые запускаются при наступлении событий, связанных с изменением структуры базы данных. Например, создании таблицы. Их также называют DDL-триггеры (Data Definition Language).
Триггеры первого типа можно разделить также:
1. По тем командам, на которые они срабатывают. Они могут относится к одной или сразу нескольким командам insert, delete, update.
2. По тому срабатывают ли они на всю команду (например update) или отдельную операцию над строкой (изменения в одной строке). Соответственно при определении триггера указывается или for each statement или for each row. В последнем случае, если команда UPDATE должна изменить 10000 строк, то триггер будет запущен 10000 раз.
По тому, срабатывают ли они перед выполнение команды (before), после выполнения команды (after) или вместо выполнения команды (instead of).
Триггеры выполняют три вида функций:
а) функция ограничения — запрет операций, не удовлетворяющих определенным требованиям;
б) расширение выполняемых операций — триггер вместе с данной операцией позволяет выполнить еще множество других;
в) журналирование операций в базе данных — триггеры позволяют легко решить вопрос о записи всех действий, изменяющих содержимое и структуру данных.
DDL-триггеры
Обратимся в начале к DDL-триггерам. Их ещё называют событийными триггерами. Сам триггер по сути состоит из двух объектов. Во-первых, это функция, которая должна выполниться при наступлении события. Это обычные хранимые функции, за исключением того, что для них указывается returns event_trigger. Вторым объектом является сам триггер, в котором указывается какое событие или когда будем обрабатывать и какую функцию нужно вызывать при наступлении события. Также можно указать фильтр для пропуска только нужных событий.
Типы событий, которые можем указать при создании триггера: ddl_command_start —триггер срабатывает перед началом DDL-события. Такие триггер ы удобны, если мы хотим запретить некоторые действия. ddl_command_end — триггер срабатывает, когда действие уже осуществилось. Этот тип обработки удобен, когда мы хотим просто регистрировать то, что происходит у нас в базе данных. sql_drop - срабатывает при удалении объектов. В том числе, например, внутри таблицы, например, при удалении столбцов. Такой триггер срабатывает до триггера ddl_command_end. table_rewrite — триггер срабатывает после перезаписи таблицы (изменении её структуры).
Поставим задачу создать триггер, который сохраняет информацию о предстоящих событиях с объектами базы данных, а также запрещает удаление таблиц. Возьмём за основу триггер ddl_command_start. Конечно, здесь есть нюанс. Он срабатывает до выполнения действия, а значит наш журнал будет хранить данные о намерениях. Не все намерения будут реализованы, в силу тех или иных причин. Запретить же выполнение данного действия очень просто: raise exception. Ниже представлена функция, которая будет выполняться перед возможным действием над объектами базы данных.
reate or replace function to_log()
returns event_trigger
as
$$
begin
if tg_tag = 'DROP TABLE' then
raise exception 'Запрет на удаление таблиц';
else
insert into log (resultat, komanda, zapros, usr, dta) values (tg_event, tg_tag, current_query(),
current_user, now());
end if;
end;
$$
language 'plpgsql';
Комментарий к представленной функции.
1. Прежде всего была создана таблица, куда будет сохраняться информация о событиях. Таблица имеет поля: resultat (tg_event) — по сути это сообщение о том, что функция сработала до команды DDL, komanda (tg_tag) — команда (CREATE TABLE, DROP TABLE и т. п. ), zapros (current_query()) — полный запрос, который должен выполниться, usr (current_user) — имя пользователя, dta (now()) — текущее время (дата и время). Для получения этих данных используются предопределённые переменные и функции.
2. С помощью tg_tag мы можем узнать какая команда выполняется. Мы перехватываем команды DROP_TABLE и отменяем их с помощью raise exception.
Для того, чтобы наш триггер заработал нужно выполнить ещё одну команду (см. ниже), в которой мы указываем что обрабатывать будем ddl_command_start и будем вызывать функцию to_log().
create event trigger ddl on ddl_command_start
execute function to_log();
Примерный результат работы нашего триггера представлен на рисунке 1.
Следующая статья по PLpgSQL
Пока всё!
Хорошего программирования. Оставляйте свои комментарии, не забывайте про лайки и подписывайтесь на мой канал programmer's notes.
