Использование триггеров для обеспечения ссылочной целостности.

Обеспечение целостности сущности и ссылочной целостность на этапе создания таблицы.

 

Вернемся к оператору CREATE TABLE. Общий формат этого оператора выглядит так:

 

CREATE TABLE имя_таблицы

(Имя_солонки тип_данных [NULL | NOT NULL] [UNIQUE] [, . . . ])

[DEFAULT значение по умолчанию ] [CHEC(условие_поиска)] {,…]}

[PRIMARY KEY (список_колонок),]

{[UNIQUE (список_солонок),] [,…]}

{[FOREIGN KEY (список_солонок)

REFERENCER имя_родительской_таблицы [(список_колонок_потенциального_ключа],

[ON UPDATE действие]

[ON DELETE действие] ] [,…]}

{[CHECK (условие поиска)] [,…] })

 

Набор доступных типов данных был описан ранее. Ключевое слово NULL используется для указания того, что в данном столбце могут содержаться значения NULL. Значение NULL отличается от пробела или нуля – оно используется для указания того, что данные недоступны, опущены или недопустимы. Если указано ключевое слово NOT NULL, то будут отклонены любые попытки поместить значение NULL в данный столбец. Если указано значение NULL, помещение значений NULL в столбец будет разрешено. По умолчанию стандарт ISO предполагает наличие ключевого слова NULL.

Столбцы первичных ключей всегда должны определяться с указанием ключевого слова NOT NULL – это гарантирует, что первичный ключ таблицы не будет содержать пустых значений, способных вызвать нарушение ссылочной целостности данных. Если определитель NOT NULL не будет указан, в ключевой столбец таблицы могут быть внесены пустые значения, что вызовет нарушение ссылочной целостности данных. Для исключения подобных ошибок потребуется создать специальные программы, предназначенные для поддержки ссылочной целостности данных в системе. Столбцы внешних ключей также часто (но не всегда) являются кандидатами на использование ключевого слова NOT NULL.

Необязательная фраза DEFAULT предназначена для задания значения, применяемого по умолчанию. Оно будет использоваться каждый раз, когда в операторе INSERT будет отсутствовать значение для данного столбца. Помимо прочих вариантов значения параметра, значение_по_умолчанию может представлять собой также литерал (числовое, строковое или дата).

Предложение CHECK было рассмотрено при определении доменов.

В предложении PRIMARY KEY определяется столбец или столбцы, образующие пер­вичный ключ таблицы. В определении каждой таблицы может присутствовать только одно предложение PRIМARY KEY. Система заблокирует выполнение любых операторов INSERT или Update, в которых предпринимается попытка создать строку с дублирующимся значением в столбце (или столбцах), указанной в предложении PRIMARY KEY. Этот меха­низм языка SQL гарантирует уникальность значений первичного ключа.

С помощью предложения UNIQUE могут определяться остальные потенциальные ключи таблицы (т.е. наборы из одного или белее столбцов, значения которых уни­кальным образом идентифицируют каждую из строк таблицы). И опять-таки, для каждого столбца, указанного в предложении UNIQUE, должен использоваться режим NOT NULL. Определение таблицы может включать произвольное количество предложе­ний UNIQUE. Система заблокирует выполнение любых операторов INSERT или UPDATE, в которых предпринимается попытка создать строку с дублирующимся значением в столбце (или столбцах), входящем в любой из потенциальных ключей таблицы.

Предложение FOREIGH KEY позволяет определять внешние ключи таблиц (дочерних) и устанавливаемую с их помощью связь с другой (родительской) таблицей. В этом предложении также указываются необходимые требования к поддержанию ссылочной целостно­сти базы данных. В общем случае предложение может включать следующие элементы:

· после фразы FOREIGN KEY список_колонок, содержащит имена одного или более столбцов таблицы, образующих данный внешний ключ;

· фразу REFERENCES, определяющую имя родительской таблицы, т.е. таблицы, содержащей соответствующий потенциальный ключ; если параметр список_колонок_потенциального_ключа (содержащий список имен одного или более столбцов родительской таблицы, образующих требуемый потенциальный ключ) опу­щен, то предполагается, что данный внешний ключ соответствует первичному ключу родительской таблицы. В этом случае также необходимо, чтобы в операторе CREATE TABLE родительской таблицы присутствовала фраза PRIMARY KEY;

· необязательную фразу определения правила обновления для данной связи (ОN UPDATE), задающую действие, которое система должна выполнить в случае обновления значения потенциального ключа родительской таблицы, на которое есть ссылки в строках, этой дочерней таблицы. Параметр действие может принимать одно из следующих значений: CASCADE, SET NULL, SET DEFAULT или NO ACTION. Если фраза ОN UPDATE опущена, по умолчанию предполагается использование правила NО ACTIOH;

· необязательную фразу определения правила удаления для данной связи (ON DELETE) задающую действие, которая, система должка выполнить в случае удаления значения потенциального ключа родительской таблицы, на кото­рое есть ссылки в строках этой дочерней таблицы; параметр referential_action принимать одно из следующих значений: CASCADE, SET NULL, SET DEFAULT или NO ACTION. Если фраза ОN UPDATE опущена, по умолчанию предполагается использование правила NО ACTIOH;

При определении таблицы предложение FOREIGN KEY может указываться произ­вольное количество раз. Предложения CHECK и CONSTRAINT позволяют задавать допол­нительные ограничения. Если они используются при введении ограничений для столбцов, то в предложении CHECK можно указывать только имя определяемого столбца.

 

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

По событию изменения ТБД триггеры различаются на вызываемые при:

· добавлении новой записи;

· изменении существующей записи;

· удалении записи.

По отношению к событию, влекущему их вызов, триггеры различаются на:

· выполняемые до наступления события;

· выполняемые после наступления события

Преимущества использования триггеров:

· автоматическое обеспечение каскадных воздействий в дочерних таблицах при изменении, удалении записи в родительской таблице выполняется на сервере. Пользователю нет необходимости заботиться о программной реализации каскадных воздействий. Поскольку каскадные воздействия выполняет сервер, нет необходимости пересылать изменения в таблицах БД из приложения на сервер, что снижает загрузку сетевого трафика;

· изменения в триггерах не влекут необходимости изменения программного кода в клиентских приложениях и не требуют распространения новых версий клиентских приложений у пользователей.

 

ЗАМЕЧАНИЕ. При откате транзакции откатываются также и все изменения, внесенные в БД триггерами.

 

Триггер создается оператором CREATE TRIGGER, которой имеет следующий формат

 

CREATE TRIGGER Имя_Триггера

FOR ИмяТаблицы [ACTIVE | INACTIVE]

{BEFORE | AFTER} {DELETE | INSERT | UPDATE}

[POSITION номер]

AS

<тело триггера>

 

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

Структура тела триггера:

 

[<объявление локальных переменных процедуры>]

BEGIN

<оператор>

END

 

Вот элементы заголовка, которые могут использоваться в определении триггеров:

· AFTER UPDATE – запуск триггера после модификации таблицы;

· AFTER INSERT – запуск триггера после вставки записи в таблицу;

· AFTER DELETE – запуск триггера после удаления записи из таблицы;

· BEFORE UPDATE – запуск триггера перед модификацией таблицы;

· BEFORE INSERT – запуск триггера перед вставкой новой записи в таблицу;

· BEFORE DELETE – запуск триггера перед удалением записи из таблицы.

 

Значение OLD.ИмяСтолбца позволяет обратиться к состоянию столбца, имевшему место до внесения возможных изменений, а значение NEW.ИмяСтолбца – к состоянию столбца после внесения возможных изменений.

 

В том случае, если значение в столбце не изменилось, OLD.ИмяСтолбца будет равно NEW.ИмяСтолбца.

 

Обеспечения каскадных воздействий с помощью триггеров. Если между двумя или более ТБД установлены отношения ссылочной целостности (отношения "один-ко-многим", "один-к-одному"), при изменении столбца связи в родительской таблице должно быть изменено значение столбца связи у записей соответствующих дочерних таблиц. Такое воздействие на дочернюю таблицу носит название каскадного обновления. Если в родительской таблице удалена запись, должны быть удалены все связанные с ней записи в дочерней таблице. Такое воздействие на дочернюю таблицу носит название каскадного удаления.

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

 

CREATE TABLE BookAuthors(

Code INTEGER NOT NULL,

…

PRIMARY KEY(Code));

 

CREATE TABLE Books(

Code INTEGER NOT NULL,

AuthorCode INTEGER NOT NULL,

…

PRIMARY KEY(Code),

FOREIGN KEY(AuthorCode) REFERENCES BookAuthors Code);

 

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

 

Триггер, реализующий каскадное обновление в дочерней таблице, будет выглядеть следующим образом

 

CREATE TRIGER UpAuthorCodeBooks FOR BookAuthors

ACTIVE

BEFORE UPDATE

AS

BEGIN

IF (OLD.Code <> NEW.Code) THEN

UPDATE Books

SET AuthorCode = NEW.Code

WHERE AuthorCode = OLD.Code

END

 

Триггер, реализующий каскадное удаление в дочерней таблице, будет выглядеть так.

 

CREATE TRIGER DelAuthorCodeBooks FOR BookAuthors

ACTIVE

AFTER DELETE

AS

BEGIN

DELETE FROM Books

WHERE AuthorCode = OLD.Code

END

 

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

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