Где и как используются триггеры, чем они отличаются от хранимых процедур, как это реализуется в Greenplum. Создание, изменение и удаление триггеров и ограничения их применения в Greenplum.
Что такое хранимые процедуры и триггеры
Напомним, хранимые процедуры представляют собой именованные блоки SQL-команд, которые заранее откомпилированы и хранятся на сервере, чтобы ускорить обработку запросов и проверку целостности данных в базе. Такое хранение и выполнение кода на сервере позволяет создавать код и переиспользовать его в разных приложениях, взаимодействующих с базой данных. При том, что триггеры и хранимые процедуры имеют общие цели, технически эти именованные блоки SQL-команд отличаются друг от друга. Хранимая процедура представляет собой именованный набор предварительно откомпилированных SQL-команд, вызываемый из клиентского приложения или из другой хранимой процедуры. Таким образом, хранимая процедура используется для реализации бизнес-логики обработки данных, тогда как триггер представляет собой частный случай хранимой процедуры, обычно используемый для технических задач. К таким задачам относится поддержка ссылочной целостности, каскадного изменения или удаления данных, их архивирования, а также отслеживания изменений или вызова хранимых процедур. В отличие от хранимой процедуры, триггер нельзя вызвать из клиентского приложения, он вызывается автоматически самой СУБД. Поэтому хранимая процедура более гибкая, ей можно передавать параметры, и она сама может возвращать параметры, значения и сообщения.
По сути, триггер представляет собой системную или пользовательскую процедуру, которая выполняется автоматически как реакция на событие с данными в существующей таблице БД (вставка, изменение или удаление), сообщая СУБД, какие действия нужно выполнить при выполнении SQL-команд INSERT, UPDATE или DELETE.
Обычно событие изменения данных, которое инициирует запуск триггера, обусловливает его название, например, insert trigger, delete trigger или update trigger. Некоторые СУБД позволяют создавать разные триггеры для предварительного или последующего выполнения этих событий. Например, BEFORE DELETE и AFTER DELETE. Однако, использовать триггер BEFORE для обращения к генерируемым (вычисляемым) столбцам нельзя, о чем мы рассказываем в новой статье.
Большинство СУБД разрешают создавать несколько триггеров для одного и того же события, но тогда надо определить порядок их выполнения. Триггеры могут опосредованно вызывать другие триггеры, если они привязаны к событиям изменения данных.
Greenplum и PostgreSQL обе поддерживают системные триггеры, а вот хранимые процедуры бизнес-логики (скрипты на стороне сервера) в этих СУБД реализуются по-разному. Например, в PostgreSQL хранимые процедуры реализованы на проприетарном языке PL/pgSQL или на других популярных языках, например, Python, подключаемых с помощью расширений, о чем мы писали здесь. Следует отметить, что Greenplum фактически нет разницы между хранимыми процедурами и функциями. При этом функция в Greenplum может выполнять запросы только для чтения к реплицированным таблицам (DISTRIBUTED REPLICATED) в сегментах, но любая SQL-команда, изменяющая данные, должна выполняться на главном экземпляре.
Также Greenplum не поддерживает пользовательские триггеры. А о том, как реализуются системные триггеры, т.е реакции на события изменения данных в таблицах, мы поговорим далее.
Триггеры в Greenplum
Как уже было отмечено ранее, Greenplum не поддерживает пользовательские триггеры, а работает только с DDL-событиями. Чтобы создать триггер на системное событие, используется команда CREATE TRIGGER. Триггер будет связан с указанной таблицей и будет запускать указанную функцию при возникновении определенных событий. Если для одного и того же события определено несколько триггеров одного типа, они будут срабатывать в алфавитном порядке по имени.
Примечательно, что из-за распределенного характера системы Greenplum использование триггеров очень ограничено. Функция, используемая в триггере, должна быть неизменяемой (IMMUTABLE), т.е. она не может использовать информацию, не представленную непосредственно в списке ее аргументов. Функция, указанная в триггере, не может запускать какой-либо SQL-команду или каким-либо образом изменять объекты распределенной базы данных. Это существенно ограничивает возможности практического применения триггеров в Greenplum. Кроме того, триггеры нельзя использовать в AO-таблицах, оптимизированных для добавления (Append-Only). Также нельзя повесить триггер на операции выборки (SELECT), поскольку эти SQL-запросы не изменяют строки. Для таких сценариев следует использовать правила и представления. Чтобы создать триггер для таблицы, пользователь должен иметь привилегию TRIGGER для этой таблицы.
При создании триггера с помощью SQL-команды CREATE TRIGGER необходимо задать следующие параметры:
- Название нового триггера (name) должно быть уникальным и отличаться от имени любого другого триггера для той же таблицы. Параметр BEFORE или AFTER определяет, вызывается ли функция до или после события. Если триггер срабатывает до события, триггер может пропустить операцию для текущей строки или изменить вставляемую строку (для операций INSERT и UPDATE). Если триггер срабатывает после события, все изменения, включая последнюю вставку, обновление или удаление, видны триггеру.
- Параметр событие (event) запускает триггер на DDL-события (INSERT, UPDATE или DELETE). Несколько событий можно указать с помощью оператора OR.
- Имя таблицы (table), которое можно дополнить названием схемы базы данных, для которой предназначен триггер.
- Параметр FOR EACH ROW или FOR EACH STATEMENT указывает, должна ли триггерная процедура запускаться один раз для каждой строки, затронутой событием триггера, или только один раз для всего SQL-оператора. Если ни один из них не указан, по умолчанию используется FOR EACH STATEMENT. Триггер с пометкой FOR EACH ROW вызывается один раз для каждой строки, которую модифицирует операция. Напротив, триггер с пометкой FOR EACH STATEMENT запускается только один раз для любой данной операции, независимо от того, сколько строк он изменяет.
- Пользовательское имя функции (funcname), которая объявлена как IMMUTABLE, не принимает аргументов и возвращает триггер, запускаемый при срабатывании триггера. Эта функция не должна запускать SQL-команды или каким-либо образом изменять базу данных.
- Аргументы (arguments) – необязательный список аргументов, разделенных запятыми, который будет предоставлен функции при запуске триггера. Аргументы являются литеральными строковыми константами и их реализация зависит от используемого языка разработки. Здесь также могут быть записаны простые имена и числовые константы, но все они будут преобразованы в строки.
К примеру, в следующем участке кода сперва объявляется функция триггера, а затем сам триггер:
CREATE FUNCTION sendmail() RETURNS trigger AS '$GPHOME/lib/emailtrig.so' LANGUAGE C IMMUTABLE; CREATE TRIGGER t_sendmail AFTER INSERT OR UPDATE OR DELETE ON mytable FOR EACH STATEMENT EXECUTE PROCEDURE sendmail();
Оператор CREATE TRIGGER в базе данных Greenplum реализует подмножество стандарта SQL, однако, при использовании триггеров необходимо учитывать следующие ограничения:
- SQL позволяет запускать триггеры при обновлении определенных столбцов, например, AFTER UPDATE OF col1, col2;
- SQL позволяет определять псевдонимы для старых и новых строк или таблиц для использования в определении запускаемого действия, например, CREATE TRIGGER… ON tablename REFERENCING OLD ROW AS somename NEW ROW AS othername… Поскольку Greenplum позволяет писать триггерные процедуры на любом количестве определяемых пользователем языков, доступ к данным осуществляется способом, зависящим от языка.
- Greenplum позволяет выполнять только UDF-функцию для инициированного действия. Стандарт ANSI SQL допускает выполнение ряда других SQL-команд, таких как CREATE TABLE, в качестве инициируемого действия. Это ограничение можно обойти, создав пользовательскую функцию, которая запускает нужные команды.
- Если запускается целая последовательность триггеров, то Greenplum запускает их в том порядке, который считает более удобным. Тогда как изначально SQL предполагает запуск триггеров в хронологическом порядке времени их создания. В частности, стандарт SQL указывает, что триггер BEFORE DELETE срабатывает при каскадных удалениях после завершения каскадного удаления. Но в Greenplum триггер BEFORE DELETE всегда срабатывает перед действием удаления, даже каскадным, что считается более последовательным.
- Расширение стандарта SQL для Greenplum позволяет указать несколько действий для одного триггера с помощью оператора OR.
Чтобы изменить свойства существующего триггера, используется команда ALTER TRIGGER. Предложение RENAME изменяет имя триггера без изменения его определения. Чтобы использовать команду ALTER TRIGGER, следует владеть таблицей, на которую действует триггер. Возможность временно активировать или деактивировать триггер предоставляется оператором ALTER TABLE, а не оператором ALTER TRIGGER, поскольку в ALTER TRIGGER нет удобного способа выразить возможность активировать или деактивировать все триггеры таблицы одновременно.
Для удаления триггера используется команда DROP TRIGGER, которая удаляет существующее определение триггера. Чтобы выполнить эту команду, текущий пользователь должен быть владельцем таблицы, для которой определен триггер.
О том, как использовать триггеры для отслеживания изменения данных, читайте в нашей новой статье.
Узнайте больше подробностей про администрирование и эксплуатацию Greenplum с Arenadata DB для эффективного хранения и аналитики больших данных на специализированных курсах в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве:
Источники
