PL/pgSQL и триггеры Определения триггеров PostgreSQL могут содержать ссылки на триггерные функции (то есть функции, которые должны вызываться при срабатывании триггера), написанные на языке PL/pgSQL Триггер определяет операцию, которая должна выполняться при наступлении некоторого события в базе данных. Не путайте определение триггера с определением триггерлой функции. Триггер определяется командой SQL CREATE TRIGGER, а триггерная функция определяется командой SQL CREATE FUNCTION. Триггеры описаны в главе 7. Триггерная функция определяется без аргументов и возвращает значение специального типа данных opaque. Синтаксис определения трнггерпой функции PL/ pgSQL командой CREATE FUNCTION приведен в листинге 11.51. Листинг 11.51. Определение триггерной функции CREATE FUNCTION функция () RETURNS opaque AS ' DECLARE объявления; [...] BEGIN команды; [...] END: ' LANGUAGE 'plpgsql': В триггерных функциях используются специальные переменные, содержащие информацию о сработавшем триггере. С помощью этих переменных триггерная функция работает с данными таблиц. Специальные переменные триггерных функций перечислены в табл. 11.2. Таблица 11.2. Специальные переменные в триггерных функциях Имя Тип данных Описание NEW RECORD Новая запись базы данных, созданная командой INSERT или UPDATE при срабатывании триггера уровня записи (ROW). Переменная используется для модификации новых записей OLD RECORD Старая запись базы данных, оставшаяся после выполнения команды INSERT или UPDATE при срабатывании триггера уровня записи (ROW) TGJAME name Имя сработавшего триггера TG_WHEN text Строка BEFORE или AFTER в зависимости от момента срабатывания триггера, указанного в определении (до или после операции) TG_LEVEL text Строка ROW или STATEMENT в зависимости от уровня триггера, указанного в определении TG_OP text Строка INSERT, UPDATE или DELETE в зависимости от операции, вызвавшей срабатывание триггера TG_RELID old Идентификатор объекта таблицы, в которой сработал триггер TG_RELNAME name Имя таблицы, в которой сработал триггер TG_NARGS Integer Количество аргументов триггерной функции, указанных в определении триггера TG_ARGV[] Массив text Аргументы, указанные в команде CREATE TRIGGER. Индексация массива начинается с нуля В листинге 11.52 приведен пример определения трнггерной функции PL/pgSDL, использующей некоторые из перечисленных переменных. Триггерная функция check_shipment_addition() вызывается после выполнения операции INSERT или UPDATE с таблицей shipments. Функция check_shipment_addition() убеждается в том, что каждая новая запись содержит действительный код покупателя и код ISBN книги. Затем общее количество экземпляров в таблице stock уменьшается на 1, если триггер сработал по команде SQL INSERT (но не по команде UPDATE!) Листинг 11.52. Триггерная функция check_shipment_addition() CREATE FUNCTION check_shipment_addition () RETURNS opaque AS ' DECLARE -- Объявление переменной для хранения кода покупателя, idjiumber integer; -- Объявление переменной для хранения кода ISBN. book_isbn text; BEGIN -- Если в таблице customers существует код. совпадающий с кодом -- покупателя в таблице new. присвоить его переменной idjiumber. SELECT INTO idjiumber id FROM customers WHERE id = NEW.customer_id: -- Если совпадение не найдено, инициировать исключение. IF NOT FOUND THEN RAISE EXCEPTION "Invalid customer ID number.": END IF; -- Если в таблице editions существует код ISBN, совпадающий с кодом -- ISBN в таблице new. присвоить его переменной bookjsbn. SELECT INTO bookjsbn isbn FROM editions WHERE isbn = NEW.isbn; -- Если совпадение не найдено, инициировать исключение. IF NOT FOUND THEN RAISE EXCEPTION "Invalid ISBN."; END IF: -- Если обе предыдущие проверки завершились успешно. -- обновить количество экземпляров. IF TG_OP - "INSERT" THEN UPDATE stock SET stock = stock -1 WHERE isbn = NEW.isbn; END IF: RETURN NEW: END; ' LANGUAGE 'plpgsql': После создания функции check_shi pment_addi ti on() в таблице shi pments устанавливается триггер для ее вызова. В листинге 11.53 приведен синтаксис команды, создающей триггер check_shipment в базе данных booktown (для клиента psql). Листинг 11.53. Триггер check_shipment booktown=# CREATE TRIGGER check_shipment booktown-* BEFORE INSERT OR UPDATE booktown-# ON shipments FOR EACH ROW booktown-* EXECUTE PROCEDURE check_shipment_addition(); CREATE Обратите внимание: функция check_shipment_addition() должна определяться в базе данных booktown до определения триггера, по которому она вызывается. Триггерные функции всегда определяются раньше триггеров. За дополнительной информацией о триггерах обращайтесь к главе 7.