Целостность данных. Диаграммы и триггеры

Цели:

1. Изучить порядок обеспечения целостности данных
2. Понятие порядок создания триггеров

Целостность данных

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

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

Для обеспечения целостности данных в SQL Server используют диаграммы и триггеры.

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

Триггеры - это аналог процедур обработчиков событий в Visual Basic. То есть они выполняют команды SQL если происходят какие-либо действия с таблицей (Например: добавление, изменение или удаление записей). При помощи триггеров можно организовать автоматическое удаление записей из вторичной таблицы при удалении связанной с ними записи из первичной таблицы.

Рассмотрим создание триггеров при помощи языка SQL.

Создание триггеров

В SQL Server существуют два вида триггеров:

1. Триггеры выполняемые после события, произошедшего с таблицей (Полный аналог процедур событий в Visual Basic);
2. Триггеры выполняемые вместо события, происходящего с таблицей. В этом случае событие (добавление, изменение или удаление записей) не выполняется, а вместо него выполняются SQL команды заданные внутри триггера.

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

Замечание: Триггеры создаются для конкретной таблицы и выполняются автоматически, если с таблицей, для которой они были созданы, происходит событие (добавление, изменение или удаление записей).

Для создания триггера на вкладке нового запроса необходимо набрать команду CREATE TRIGGER, имеющую следующий синтаксис:

CREATE TRIGGER <Имя триггера>
ON <Имя таблицы>
FOR <INSERT|UPDATE|DELETE>
[WITH ENCRYPTION]
AS <Команды SQL>

Здесь:

• Имя триггера - это имя создаваемого триггера.
• Имя таблицы - имя таблицы, для которой создаётся триггер.
• Если используется параметр AFTER, то триггер выполняется после события, а если параметр INSTEAD OF, то выполняется вместо события.
• Параметры INSERT, UPDATE и DELETE определяют событие, при котором (или вместо которого) выполняется триггер.
• Параметр WITH ENCRYPTION - предназначен для включения шифрования данных при выполнении триггера.
• Команды SQL - это SQL команды, выполняемые при активизации триггера.

Рассмотрим примеры создания различных триггеров для таблицы "Студенты".

Пример: Создаёт триггер "Добавление", выводящий на экран сообщение "Запись добавлена" при добавлении новой записи в таблицу "Студенты"

CREATE TRIGGER Добавление
ON Студенты
FOR AFTER INSERT
AS PRINT 'Запись добавлена'

Пример: Создаёт триггер "Изменение", выводящий на экран с сообщение "Запись изменена" при изменении записи в таблице "Студенты"

CREATE TRIGGER Изменение
ON Студенты
FOR AFTER UPDATE
AS PRINT 'Запись изменена'

Пример: Создаёт триггер "Удаление", выводящий на экран с сообщение "Запись удалена" при удалении записи из таблицы "Студенты"

CREATE TRIGGER Удаление
ON Студенты
FOR AFTER DELETE
AS PRINT 'Запись удалена'

Пример: В данном примере вместо удаления студента из таблицы "Студенты" выполняется код между BEGIN и END. Он состоит из двух команд DELETE. Первая команда удаляет все записи из таблицы "Оценки", которые связаны с записями из таблицы "Студенты". То есть у которых Оценки.[Код студента] равен коду удаляемого студента. Затем из таблицы "Студенты" удаляется сам студент.

CREATE TRIGGER УдалениеСтудента
ON Студенты
INSTEAD OF DELETE
AS
BEGIN
DELETE Оценки
FROM deleted
WHERE deleted.[Код студента]=Оценки.[Код студента]
DELETE Студенты
FROM deleted
WHERE deleted.[Код студента]=Студенты.[Код студента]
END

Замечание: Здесь удаляемая запись обозначается служебным словом deleted.

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

На этом мы заканчиваем рассмотрение диаграмм и триггеров. Дополнительную информацию можно найти в "лабораторной работе №7" .