Лабораторная работа: Диаграммы и триггеры

Цель: научиться создавать диаграммы и триггеры Перейдем теперь к созданию диаграмм. В БД «Microsoft SQL Server 2008» все диаграммы находятся в папке «Database Diagrams» обозревателя объектов (рис. 14.1).

Рис. 14.1.

Создадим диаграмму, обеспечивающую целостность данных нашей БД «Students». Для создания новой диаграммы в БД «Students» щелкните ПКМ по папке «Database Diagrams» и в появившемся меню выберем пункт «New Database Diagram». Сначала появится окно с вопросом о добавлении нового объекта «Диаграмма». В этом окне нужно нажать кнопку «Yes». Затем появится окно «Add Table» предназначенное для добавления таблиц в новую диаграмму (рис. 14.2).

Рис. 14.2.

В окне добавления таблиц выделите все таблицы нашей БД и нажмите кнопку «Add» (рис. 14.2). Закройте окно «Add Table» нажатием на кнопку «Close» .

Появится окно диаграммы, где будут отображены отобранные таблицы. Теперь необходимо определить связи между таблицами. Перетащите поле «Код специальности» из таблицы «Специальности» на такое же поле в таблице «Студенты». Появится окно создания связи между таблицами «Tables and Columns» (рис. 14.3).

Рис. 14.3.

В окне создания связи нажмите кнопку «Ok». Появится окно настройки свойств связи «Foreign Key Relationship» (рис. 14.4).

Рис. 14.4.

Оставьте свойства связи без изменений и в окне свойств связи нажмите кнопку «Ok» .В диаграмме между таблицами «Студенты» и «Специальности» появится связь в виде ломанной линии (рис. 14.5).

Аналогичным образом создайте связь таблицы «Студенты» с таблицей «Оценки», перетащив поле «Код студента» из таблицы «Студенты» на одноименное поле в таблице «Оценки». Затем, свяжите таблицы" Предметы" и «Оценки», перетащив поле «Код предмета» из таблицы «Предметы» на поля «Код предмета 1″ ,» Код предмета 2″ и «Код предмета 3» таблицы «Оценки». После выполнения вышеперечисленных действий диаграмма примет следующий вид (рис. 14.5).

Закройте окно с диаграммой, щелкнув мышью по кнопке закрытия расположенной в верхнем правом углу окна с диаграммой. Появится окно с вопросом о сохранении новой диаграммы, где необходимо нажать кнопку «Yes» (рис. 14.6).

Рис. 14.6.

Появится окно определения имени новой диаграммы «Choose Name» .

В окне определения имени, задайте имя диаграммы как «Диаграмма БД Студенты» и нажмите кнопку «Ok» (рис. 14.7).

Рис. 14.7.

Появится окно «Save» с запросом сохранения таблиц, входящих в диаграмму. В данном окне необходимо нажать кнопку «Yes» (рис. 14.8).

Рис. 14.8.

Перейдем к созданию триггеров. Создадим триггеры для таблицы «Студенты». Триггеры создаются отдельно для каждой таблицы и располагаются в обозревателе объектов в папке «Triggers». В нашем случае, папка «Triggers» входит в состав таблицы «Студенты» (рис. 14.9).

Рис. 14.9.

Для начала создадим триггер, выводящий сообщение «Запись добавлена» при добавлении записи в таблицу «Студенты». Создадим новый триггер, щелкнув ПКМ по папке «Triggers» в таблице «Студенты» и в появившемся меню выбрав пункт «New Trigger». Появится следующее окно с новым триггером (рис. 14.10):

Рис. 14.10.

Рассмотрим структуру триггеров:

Область определения имени функции (Trigger_Name);

Область, показывающая для какой таблицы создается триггер (Table_Name);

Область, показывающая когда выполнять триггер (INSERT — при создании записи в таблице, DELETE — при удалении и UPDATE — при изменении) и как его выполнять (AFTER — после выполнения операции, INSTEAD OF — вместо выполнения операции);

Тело триггера, содержит команды языка программирования запросов T-SQL.

В окне нового триггера наберите код как показано на рис. 14.11.

Рис. 14.11.

Из рис. 14.11 видно, что создаваемый триггер «Индикатор добавления» выполняется после добавления записи (AFTER INSERT) в таблицу «Студенты» (ON dbo. Студенты). После добавления записи триггер выведет на экран сообщение «Запись добавлена» (PRINT 'Запись добавлена'). Выполните набранный код, нажав кнопку на панели инструментов. В нижней части окна с кодом появится сообщение «Command (s) completed successfully.» .

Проверим, как работает новый триггер. Создайте новый пустой запрос и в нем наберите следующую команду для добавления новой записи в таблицу «Студенты» (рис. 14.12):

Рис. 14.12.

Выполните набранную команду, нажав кнопку на панели инструментов. В таблицу будет добавлена новая запись, и триггер выведет сообщение «Запись добавлена» (рис. 14.12).

Теперь создадим триггер отображающий сообщение «Запись изменена». Создайте новый триггер, как в предыдущем случае. В окне нового триггера наберите следующий код (рис. 14.13):

Рис. 14.13.

Из рис. 14.13 видно, что новый триггер «Индикатор изменения» выполняется после изменения записи (AFTER UPDATE) в таблице «Студенты» (ON dbo. Студенты). После изменения записи триггер выведет на экран сообщение «Запись изменена» (PRINT 'Запись изменена'). Выполните набранный код. В нижней части окна с кодом появится сообщение «Command (s) completed successfully.» .

Проверим работоспособность созданного триггера. Создайте новый запрос и в нем наберите команду, представленную на рис. 14.14.

Рис. 14.14.

Выполните набранную команду, нажав кнопку на панели инструментов. В таблицу будет добавлена новая запись, и триггер выведет сообщение «Запись изменена» (рис. 14.14).

Для полноты картины создадим триггер, выводящий сообщение при удалении записи из таблицы «Студенты». Создайте новый триггер и в нем наберите код, показанный на рис. 14.15.

Рис. 14.15.

Создаваемый триггер «Индикатор удаления» выполняется после удаления записи (AFTER DELETE) из таблицы студенты (ON dbo. Студенты). После удаления записи триггер выводит сообщение «Запись удалена» (PRINT 'Запись удалена').

Выполните код, представленный рис. 14.15. В нижней части окна с кодом появится сообщение «Command (s) completed successfully.» .

Проверим работу триггера «Индикатор удаления» удалив созданную ранее запись из таблицы «Студенты». Для этого создайте новый запрос и в нем наберите следующую команду (рис. 14.16):

Рис. 14.16.

Выполните вышеприведенную команду. После удаления записи триггер «Индикатор удаления» отобразит сообщение «Запись удалена» (рис. 14.16).

В заключение рассмотрим пример применения триггеров для обеспечения целостности данных. Создадим триггер «Удаление студента», который при удалении записи из таблицы студенты сначала удаляет все связанные с ней записи из таблицы «Оценки», а затем удаляет саму запись из таблицы «Студенты», тем самым обеспечивается целостность данных.

Создайте новый триггер и в нем наберите следующий код (рис. 14.17):

Рис. 14.17.

Создаваемый триггер «Удаление студента» выполняется вместо удаления записи (INSTEAD OF DELETE) из таблицы «Студенты» (ON dbo. Студенты).

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

После срабатывания триггера из таблицы «Оценки» удаляется запись, у которой значение поля «Код студента» равно значению такого же поля у удаляемой записи из таблицы «Студенты». Эту операцию выполняют следующие команды:

DELETE FROM dbo.Оценки.

WHERE Deleted. Код студента] = Оценки. Код студента].

Затем удаляется запись из таблицы «Студенты», которую удаляли до срабатывания триггера. Удаление выполняется следующими командами:

DELETE dbo.Студенты.

FROM Deleted.

WHERE Deleted. Код студента] = Студенты. Код студента].

Выполните код, представленный на рис. 14.17. В нижней части окна с кодом появиться сообщение «Command (s) completed successfully.» .

Проверим, как работает триггер «Удаление студента». Для этого создайте новый запрос и в нем наберите следующий код (рис. 14.18):

Рис. 14.18.

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

Замечание: Хотелось бы заметить, что без использования триггера «Удаление студента» нам бы не удалось удалить запись из таблицы «Студенты». Команда удаления была бы заблокирована диаграммой «Диаграмма БД Студенты» во избежание нарушения целостности данных.

На этом мы завершаем работу с диаграммами и триггерами. После выполнения всех вышеописанных действий обозреватель объектов будет иметь следующий вид (рис. 14.19):

Рис. 14.19.