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.
|