Самоучитель по SQL-сервер в Linux



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.