Название: Базы данных. Концепция баз данных, реляционная модель данных, языки SQL и XML (Токмаков Г. П.)

Жанр: Информационные системы и технологии

Просмотров: 1401


6.2. триггеры

 

Довольно часто перед некоторыми событиями SQL или после них должны выполняться определенные операции − например, проверка логической целост- ности данных, заносимых в базу, предварительное форматирование данных пе- ред вставкой или модификация других таблиц, логически обусловленная уда- лением или модификацией записей. Традиционно такие операции выполнялись на программном уровне приложением, подключившимся к базе данных, а не самой СУБД.

В  PostgreSQL поддерживаются  нестандартные  расширения,  называемые триггерами (trigger) и упрощающие взаимодействие приложения с базой дан-

ных. Триггер определяет функцию, которая должна выполняться до или после некоторой  операции  с  базой  данных.  Триггеры  реализуются  на  языке  С, PL/pgSQL или любом другом функциональном языке (кроме SQL), который мо- жет использоваться в PostgreSQL для определения функций.

 

ВНИМАНИЕ. Триггеры относятся к числу специфических расширений PostgreSQL, поэтому их не рекомендуется использовать в решениях, требующих высокой степени совместимости с другими РСУБД.

 

Триггеры срабатывают при выполнении с таблицей команды SQL INSERT,

UPDATE или DELETE.

 

6.2.1.  С О З ДАНИ Е  ТРИГГЕРА

 

Триггер создается на основе существующей функции. PostgreSQL позволяет создавать функции на разных языках программирования, в том числе на SQL, PL/pgSQL и С. В PostgreSQL 7.1.x триггеры могут вызывать функции, написан- ные на любом языке, но за одним исключением: функция не может быть полно- стью реализована на SQL.

В определении триггера указывается, должна ли заданная функция вызы- ваться до или после выполнения некоторой операции с таблицей. Синтаксис определения триггера выглядит так:

CREATE TRIGGER триггер { BEFORE | AFTER } { событие [ OR событие

...]}

ON таблица

FOR EACH { ROW STATEMENT }

EXECUTE PROCEDURE функция ( аргументы )

 

Ниже приводятся краткие описания компонентов этого определения.

– CREATE TRIGGER триггер. В аргументе триггер указывается произвольное имя создаваемого триггера. Имя может совпадать с именем триггера, уже существую-

щего в базе данных — при условии, что этот триггер установлен для другой таб- лицы. Кроме того, по аналогии с большинством других несистемных объектов баз данных, имя триггера (в сочетании с таблицей, для которой он устанавливается)

должно быть уникальным лишь в контексте базы данных, в которой он создается.

– {BEFORE AFTER}. Ключевое слово BEFORE означает, что функция должна вы-

полняться перед попыткой выполнения операции, включая все встроенные про-

верки ограничений данных, реализуемые при выполнении команд INSERT и DELETE. Ключевое слово AFTER означает, что функция вызывается после завершения опе- рации, приводящей в действие триггер.

– {событие [OR событие ... ]}. События SQL, поддерживаемые в PostgreSQL При перечислении нескольких событий в качестве разделителя используется ключевое слово OR.

– ON таблица. Имя таблицы, модификация которой заданным событием при-

водит к срабатыванию триггера.

– FOR EACH {ROW STATEMENT}. Ключевое слово, следующее за конструкцией FOR EACH и определяющее количество вызовов функции при наступлении указанного

события. Ключевое слово ROW означает, что функция вызывается для каждой мо- дифицируемой записи. Если функция должна вызываться всего один раз для всей команды, используется ключевое слово STATEMENT.

– EXECUTE PROCEDURE функция ( аргументы ). Имя вызываемой функции с ар-

гументами.

 

ПРИМЕЧАНИЕ. Создание триггеров разрешено только владельцу базы данных или суперполь-

зователю.

 

Механизм ограничений PostgreSQL позволяет реализовать простое сравне- ние данных со статическими значениями, но иногда проверка входных данных должна производиться по более сложным критериям. Это типичный пример си- туации, в которой удобно воспользоваться триггером.

Проверка входных данных с применением триггеров может осуществляться перед вставкой данных в таблицу или перед их обновлением в таблице. Функ-

ция триггера может убедиться в том, что новые данные удовлетворяют сложной системе ограничений, и даже вернуть признак ошибки через систему регистра- ции ошибок PostgreSQL.

Предположим, вы написали на процедурном языке функцию, которая про- веряет данные, переданные при вызове команды INSERT или UPDATE для таблицы shipments, и затем обновляет таблицу stock, снимая поставленный товар со

складского учета. Такую функцию можно написать на любом языке, поддержи-

ваемом PostgreSQL (кроме «чистого» SQL, о чем говорилось выше).

Прежде всего функция убеждается в том, что переданный код покупателя (customer_id) и код ISBN (isbn) присутствуют в таблицах customers и editions. Если хотя бы один из кодов отсутствует, функция возвращает признак ошибки. Если оба кода присутствуют в таблицах, команда SQL выполняется, и после ус- пешного завершения количество товара на складе в таблице stock автоматиче- ски уменьшается в соответствии с объемом поставки.

Триггер, создаваемый в листинге 6.44, срабатывает непосредственно перед выполнением команды INSERT или UPDATE в таблице shipments. Триггер вызыва- ет функцию check_shipment _addition() для каждой изменяемой записи.

 

Листинг 6.44. Создание триггера 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 настроен  на  выполнение функции check_shipment_addition() для команд INSERT и UPDATE, поэтому он достаточно надежно обеспечивает логическую целостность данных в полях customer_id и isbn. Ключевое слово ROW гарантирует, что каждая добавляемая или модифици- руемая запись будет обработана функцией проверки check_argument_ addition().

Функция check_shipment_addition() вызывается без аргументов, посколь-

ку для проверки записей в ней используются внутренние переменные PL/pgSQL.

 

6.2.2.  П ОЛУЧЕ НИЕ  ИНФ ОРМАЦ И И  О  ТРИГ Г ЕРАХ

 

В PostgreSQL триггеры хранятся в системной таблице pg_trigger, что позво- ляет получить информацию о существующих триггерах на программном уровне. Структуру таблицы pg_trigger иллюстрирует таблице 6.2.

 

 

Таблица pgjrigger

 

Таблица 6.2

 

Поле

Тип

TGRELID

oid

TGNAME

name

TGFOID

oid

TGTYPE

smallint

TGENABLED

boolean

TGISCONSTRAINT

boolean

TGCONSTRNAME

name

TGCONSTRRELID

oid

TGDEFERRABLE

boolean

Окончание табл. 6.2

TGINLTDEF ERRED

boolean

TGNARGS

smallint

TGATTR

int2vector

TGARGS

bytea

 

Большинство полей, перечисленных в таблице 6.2, в прямых запросах не используется. Среди атрибутов триггеров в системной таблице pg_trigger цен- тральное место занимают атрибуты tgrelid и tgname.

В поле tgrelid хранится идентификатор отношения, с которым связан дан- ный триггер. Значение относится к типу oid и соответствует содержимому поля relfilenode системной таблицы pg_class. В поле tgname хранится имя тригге-

ра, указанное в команде CREATE TRIGGER при его создании.

 

6.2.3.  У ДАЛЕНИЕ  ТРИГГЕРА

 

Команда DROP TRIGGER удаляет триггер из базы данных. Удаление тригге- ров, как и их создание командой CREATE TRIGGER, может выполняться только владельцем триггера или суперпользователем.

Синтаксис удаления существующих триггеров:

 

DROP TRIGGER имя ON таблица

 

В листинге 6.45 приведен пример удаления триггера check_shipment, уста-

новленного для таблицы shipments.

 

Листинг 6.45. Удаление триггера

 

booktown=# DROP TRIGGER check_shipment ON shipments: DROP

 

Сообщение DROP означает, что триггер успешно удален. Обратите внима- ние: при удалении указывается не только имя удаляемого триггера, но и имя таблицы.

Если вы не помните, в какой таблице был установлен удаляемый триггер, необходимую информацию можно получить из системных таблиц PostgreSQL. Например, можно провести объединение полей  tgrelid системной таблицы

pg_trigger и поля relfilenode системной таблицы pg_class и сравнить имя триггера с полем tgname. Запрос, приведенный в листинге 6.46, возвращает имя отношения (rel name), связанного с триггером check_shipment.

 

Листинг 6.46. Получение имени таблицы, связанной с триггером

 

booktown=# SELECT relname FROM pg_class booktown‐# INNER JOIN pg_trigger booktown‐# ON (tgrelid = relfilenode)

booktown‐# WHERE tgname = 'check_shipment': . relname

shipments

(1 row)

ВНИМАНИЕ. При удалении функции, вызываемой по срабатыванию триггера, триггер пере- стает работать, причем повторное определение функции с тем же именем не исправит проблему. После повторного создания функции триггер также приходится создавать заново.

 

6.2.4.  PL/ PG SQL И  ТРИГ Г ЕРЫ

 

Определения триггеров PostgreSQL могут содержать ссылки на триггерные функции (то есть функции, которые должны вызываться при срабатывании триггера), написанные на языке PL/pgSQL. Триггер определяет операцию, кото- рая должна выполняться при наступлении некоторого события в базе данных.

Не  путайте определение триггера с  определением триггерной функции.

Триггер определяется командой SQL CREATE TRIGGER, а триггерная функция оп-

ределяется командой SQL CREATE FUNCTION.

Триггерная функция определяется без аргументов и возвращает значение специального типа данных opaque. Синтаксис определения трнггерпой функции

PL/pgSQL командой CREATE FUNCTION приведен в листинге 6.47.

 

Листинг 6.47. Определение триггерной функции

 

CREATE FUNCTION функция () RETURNS opaque AS ' DECLARE

объявления; [...]

BEGIN команды; [...]

END;

' LANGUAGE 'plpgsql':

 

В триггерных функциях используются специальные переменные, содержа- щие информацию о сработавшем триггере. С помощью этих переменных триг- герная функция работает с данными таблиц. Специальные переменные триг- герных функций перечислены в таблице 6.3.

 

 

Специальные переменные в триггерных функциях

 

Таблица 6.3

 

Имя

Тип данных

Описание

NEW

RECORD

Новая запись базы данных, созданная командой IN‐ SERT или UPDATE при срабатывании триггера уровня записи (ROW). Переменная используется для моди‐ фикации новых записей

OLD

RECORD

Старая запись базы данных, оставшаяся после вы‐ полнения команды INSERT или UPDATE при срабаты‐ вании триггера уровня записи (ROW)

TGNAME

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.

Индексация массива начинается с нуля

 

В листинге 6.48 приведен пример определения трнггерной функции PL/pgSDL, использующей некоторые из перечисленных переменных. Триггерная функция check_shipment_addition() вызывается после выполнения операции INSERT или UPDATE с таблицей shipments.

Функция check_shipment_addition() убеждается в том, что каждая новая запись содержит действительный код покупателя и код ISBN книги. Затем об- щее количество экземпляров в таблице stock уменьшается на 1, если триггер

сработал по команде SQL INSERT (но не по команде UPDATE!)

 

Листинг 6.48. Триггерная функция check_shipment_addition()

 

CREATE FUNCTION check_shipment_addition () RETURNS opaque AS ' DECLARE

‐‐ Объявление переменной для хранения кода покупателя.

id_number integer;

‐‐ Объявление переменной для хранения кода ISBN.

book_isbn text; BEGIN

‐‐ Если в таблице customers существует код, совпадающий с кодом

‐‐ покупателя в таблице new, присвоить его переменной id_number.

SELECT          INTO   id_number        id         FROM customers         WHERE           id         = NEW.customer_id;

‐‐ Если совпадение не найдено, инициировать исключение.

IF NOT FOUND THEN

RAISE EXCEPTION "Invalid customer ID number.";

END IF;

‐‐ Если в таблице editions существует код ISBN, совпадающий

‐‐ с кодом ISBN в таблице new, присвоить его переменной book_isbn.

SELECT INTO book_isbn 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_shipment_addition() в таблице shipments устанавливается триггер для ее вызова. В листинге 6.49 приведен синтаксис команды, создающей триггер check_shipment в базе данных booktown (для кли- ента psql).

 

Листинг 6.49. Триггер 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 до определения триггера, по которому она вызы- вается. Триггерные функции всегда определяются раньше триггеров.

 

 

 

 

 

 

 

 
Современные информационные системы гетерогенны и форматы хранения данных, определяемые реляционными СУБД, не являются единственными в своем роде. Например, данные о читателях библиотеки могут сопровождаться, используя электронные таблицы Excel, а отдел кадров может хранить инфор- мацию о студентах, которые одновременно являются читателями той же биб- лиотеки в формате, определяемой реляционной СУБД, например, PostgreSQL. В случае зачисления студентов на первый курс сведения о студентах, введен- ные в базу данных отдела кадров, могут понадобиться библиотеке. Но в этом случае возникает проблема обмена данными. Дело в том, что электронные таб- лицы Excel не могут адекватно отобразить данные формата PostgreSQL.

Здесь на простом примере обозначена проблема совместимости форматов.

Для решения этой проблемы предложено решение − хранение данных в форма-

те XML. Так называется стандартный язык для представления и обмена структу- рированными данными. На сегодняшний день XML широко используется в Web‐ среде, выступая промежуточным универсальным форматом хранения данных.

XML представляет собой структуру и правила описания любой информации содержательным способом. Используя XML, вы можете создать свой язык раз-

метки для представления информации любого вида. XML  это одна из самых важных новейших технологий, порожденных развитием Интернета.

XML  это самостоятельная технология, хотя и возникшая исторически как

Internet-технология. Представляется важным рассмотреть технологию XML в отрыве от Internet в связи с технологиями баз данных. Поскольку и SQL, и XML

является языками определения структурированных данных, можно предполо-

жить, что между этими двумя языками должна существовать взаимосвязь.

Вопрос заключается, в том как эти языки соотносятся друг к другу  нахо- дятся ли эти языки в конфликте или дополняют друг друга. Как связан язык XML с реляционным языком запросов SQL, можно ли на основе XML и построить модель данных, что собой представляет XML-ориентированная база данных, существуют

- 153-