Лекция 11 ООП. Лабораторная работа 11. Лабораторная работа 6 работа с триггерами в sql server management studio
 Скачать 424.37 Kb.
|
|
1 ЛАБОРАТОРНАЯ РАБОТА 6 РАБОТА С ТРИГГЕРАМИ В SQL SERVER MANAGEMENT STUDIO Цель работы: ознакомление студентов с понятием триггера, изучение спо- собов создания триггеров на языке Transact-SQL, а также приобретение практи- ческих навыков работы с триггерами в среде SQL Server Management Studio. 1. Понятие триггера Триггер – это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Триггер представляет собой специальный тип хранимых процедур, запус- каемых сервером автоматически при попытке изменения данных в таблицах, с которыми триггеры связаны. Каждый триггер привязывается к конкретной таб- лице. Все производимые им модификации данных рассматриваются как одна транзакция. В случае обнаружения ошибки или нарушения целостности дан- ных происходит откат этой транзакции. Тем самым внесение изменений запре- щается. Отменяются также все изменения, уже сделанные триггером. Триггеры – особый инструмент SQL-сервера, используемый для поддержа- ния целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответ- ствии с правилами, стандартными значениями и т.д. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными за- тратами ресурсов на операции ввода/вывода. В том случае, когда тех же резуль- татов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, примене- ние триггеров нецелесообразно. В отличие от обычной подпрограммы, триггер выполняется неявно в каж- дом случае возникновения триггерного события, к тому же он не имеет аргу- ментов. Приведение его в действие иногда называют запуском триггера. С помощью триггеров достигаются следующие цели: − проверка корректности введенных данных и выполнение сложных огра- ничений целостности данных, которые трудно, если вообще возможно, под- держивать с помощью ограничений целостности, установленных для таблицы; − выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном определенным образом; 2 − накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили. При условии правильного использования триггеры могут стать очень мощным механизмом. Основное их преимущество заключается в том, что стан- дартные функции сохраняются внутри базы данных и согласованно активизи- руются при каждом ее обновлении. Это может существенно упростить прило- жения. Тем не менее, следует упомянуть и о присущих триггеру недостатках: − сложность: при перемещении некоторых функций в базу данных усложняются задачи ее проектирования, реализации и администрирования; − скрытая функциональность: перенос части функций в базу данных и со- хранение их в виде одного или нескольких триггеров иногда приводит к сокры- тию от пользователя некоторых функциональных возможностей. Хотя это в определенной степени упрощает его работу, но, к сожалению, может стать при- чиной незапланированных, потенциально нежелательных и вредных побочных эффектов, поскольку в этом случае пользователь не в состоянии контролиро- вать все процессы, происходящие в базе данных; − влияние на производительность: перед выполнением каждой команды по изменению состояния базы данных СУБД должна проверить триггерное условие с целью выяснения необходимости запуска триггера для этой команды. Выполнение подобных вычислений сказывается на общей производительности СУБД, а в моменты пиковой нагрузки ее снижение может стать особенно за- метным. Очевидно, что при возрастании количества триггеров увеличиваются и накладные расходы, связанные с такими операциями. 2. Создание, изменение и удаление триггера В среде MS SQL Server принят следующий синтаксис команды создания или изменения триггера: {CREATE | ALTER} TRIGGER <имя_триггера> ON <имя таблицы_или_представления> {AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]} AS <операторы_SQL> Триггер может быть создан только в текущей базе данных, но допускается обращение внутри триггера к другим базам данных, в том числе и расположен- ным на удаленном сервере. Имя триггера должно быть уникальным в пределах базы данных. В SQL Server существует два параметра, определяющих поведение триг- геров: − AFTER. Триггер выполняется после успешного выполнения вызвавших его команд модификации данных. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате выполнения запроса пользователя и выпол- нение триггера осуществляется в теле одной транзакции: если произойдет от- 3 кат триггера, то будут отклонены и пользовательские изменения. Можно опре- делить несколько AFTER-триггеров для каждой операции (INSERT, UPDATE, DELETE). Если для таблицы предусмотрено выполнение нескольких AFTER- триггеров, то с помощью системной хранимой процедуры sp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server все триггеры являются AFTER-триггерами. − INSTEAD OF. Триггер вызывается вместо выполнения команд. В отли- чие от AFTER-триггера INSTEAD OF-триггер может быть определен как для таблицы, так и для представления. Для каждой INSERT, UPDATE, DELETE можно определить только один INSTEAD OF-триггер. Триггеры различают по типу команд, на которые они реагируют. Суще- ствует три типа триггеров: − INSERT TRIGGER – запускаются при попытке вставки данных с помо- щью команды INSERT; − UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE; − DELETE TRIGGER – запускаются при попытке удаления данных с по- мощью команды DELETE. Конструкции {INSERT, UPDATE, DELETE}определяют, на какую коман- ду будет реагировать триггер. При создании триггера должна быть указана хотя бы одна команда, однако допускается создание триггера, реагирующего на две или на все три команды. При выполнении команд добавления, изменения и удаления записей сер- вер создает две специальные таблицы: inserted и deleted. В них содержатся строки, которые будут вставлены или удалены по завершении транзакции. Структура таблиц inserted и deleted идентична структуре таблиц, для которой определяется триггер. Для каждого триггера создается свой комплект таблиц inserted и deleted, поэтому никакой другой триггер не сможет получить к ним доступ. В зависимости от типа операции, вызвавшей выполнение триггера, содер- жимое таблиц inserted и deleted может быть разным: − команда INSERT – в таблице inserted содержатся все строки, которые пользователь пытается вставить в таблицу. В таблице deleted не будет ни одной строки. После завершения триггера все строки из таблицы inserted переместятся в исходную таблицу. − команда DELETE – в таблице deleted будут содержаться все строки, ко- торые пользователь попытается удалить. Триггер может проверить каждую строку и определить, разрешено ли ее удаление. В таблице inserted не окажется ни одной строки. − команда UPDATE – в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера. 4 Для получения информации о количестве строк, которое будет изменено при успешном завершении триггера, можно использовать функцию @@ROWCOUNT; она возвращает количество строк, обработанных последней командой. Следует помнить, что триггер запускается не при попытке изменить конкретную строку, а в момент выполнения команды изменения. Одна такая команда воздействует на множество строк, поэтому триггер должен обрабаты- вать все эти строки. Если триггер обнаружил, что из 100 вставляемых, изменяемых или удаля- емых строк только одна не удовлетворяет тем или иным условиям, то никакая строка не будет вставлена, изменена или удалена. Такое поведение обусловлено требованиями транзакции – должны быть выполнены либо все модификации, либо ни одной. Триггер выполняется как неявно определенная транзакция, поэтому внут- ри триггера допускается применение команд управления транзакциями. В част- ности, при обнаружении нарушения ограничений целостности для прерывания выполнения триггера и отмены всех изменений, которые пытался выполнить пользователь, необходимо использовать команду ROLLBACK TRANSACTION. Для удаления триггера используется команда: DROP TRIGGER <имя_триггера> 3. Работа с триггерами в SQL Server Management Studio Создадим триггер для таблицы Student. Триггеры создаются отдельно для каждой таблицы и располагаются в обозревателе объектов в папке «Триггеры». В нашем случае, папка «Триггеры» входит в состав таблицы «Student» (рис.1). Рис. 1 Триггеры в обозревателе объектов 5 Предположим, что при формировании студенческих учебных групп суще- ствует правило, в соответствии с которым количество студентов в группе не должно превышать 25 человек. Требуется создать триггер, который при добав- лении записи в таблицу «Student» определяет количество студентов в той груп- пе, в которую определен новый студент, и в случае невыполнения вышеуказан- ного требования, отменяет операцию добавления и выводит соответствующее сообщение «Группа уже полностью сформирована». Для создания нового триггера, щелкните правой кнопкой мыши по папке «Триггеры» в таблице «Student» и в появившемся меню выберите пункт «Со- здать триггер…». Появится окно кода нового триггера (рис. 2). Рис. 2 Окно для ввода кода триггера Рассмотрим структуру триггеров: 1. Область определения имени триггера. 2. Область, показывающая для какой таблицы создаётся триггер. 3. Область, показывающая когда выполнять триггер (INSERT – при созда- нии записи в таблице, DELETE – при удалении и UPDATE – при изменении) и как его выполнять (ALTER – после выполнения операции, INSTEAD OF – вме- сто выполнения операции). 4) Тело триггера, содержит команды языка программирования запросов T- SQL. Код триггера, выполняющей необходимые действия, представлен на ри- сунке 3. 6 Рис. 3 Код триггера Из рис.3 видно, что создаваемый триггер с именем CheckCountStudent_ins_after выполняется сразу после добавления записи (AFTER INSERT) в таблицу «Student» (ON Student). Выполните код триггера, нажав на панели инструментов кнопку «Выпол- нить» или клавишу F5 (рис. 3). В нижней части окна с кодом появится сообще- ние «Выполнение команд успешно завершено». Закройте окно с кодом, щёлк- нув мышью по кнопке закрытия, расположенной в верхнем правом углу окна с кодом процедуры. Проверим работоспособность созданного триггера. Для этого необходимо создать новый пустой запрос, нажав на кнопку на панели ин- струментов. В появившемся окне с пустым запросом наберите оператор добав- ления нового студента (см. рис.4) и выполните его. Рис. 4 Результат срабатывания триггера при отсутствии ошибок Результат выполнения оператора и срабатывания триггера появится в ниж- ней части окна с кодом (рис. 4). При попытке добавить студента в заполненную группу, результат будет выглядеть следующим образом (рис.5). 7 Рис. 5 Результат срабатывания триггера при наличии ошибок Изменим код триггера таким образом, чтобы проверка выполнялась до до- бавления (INSTEAD OF INSERT) записи в таблицу (рис.6), и выполним его. Рис. 6 Код триггера INSTEAD OF INSERT Для проверки триггера необходимо выполнить предыдущие команды до- бавления записей в таблицу Student, как представлено на рис.4 и рис.5. 4. Задания на лабораторную работу Задание 1. Изучить описание лабораторной работы № 5. Задание 2. По указанию преподавателявыполнить одно или два из ниже- перечисленных заданий. 2.1. а) Создать триггер «Удаление группы», который при удалении данных о группе, сначала удаляет все связанные с ней записи из таблицы Student, а затем удаляет саму запись из таблицы StudGroup, тем самым обеспечива- ется целостность данных. Предварительно удалите связь между таблицами Student и StudGroup (внешний ключ). Реализуйте в триггере возможность множественного удаления данных. б) Создать триггер «Удаление лектора», который при удалении данных о преподавателе, сначала изменяет все связанные с ним записи в таблице Ex- 8 am, устанавливая значения поля id_Lect равное NULL, а затем удаляет са- му запись преподавателя из таблицы Lecturer. Предварительно удалите связь между таблицами Lecturer и Exam (внешний ключ). Реализуйте в триггере возможность множественного удаления данных. 2.2. а) Создать триггер, который при добавлении или изменении данных в таб- лице Exam, проверяет правильность введенных id_Student и id_Lect (их наличие в таблицах Student и Lecturer). Предварительно проверить, что вставляется или обновляется только од- на строка и удалите связь между указанными таблицами (внешние ключи). б)** Создать триггер, который при добавлении или изменении данных в таблице «Student», проверяет правильность введенной группы (ее наличие в таблице StudGroup) и отсутствие в группе полных однофамильцев. Предварительно проверить, что вставляется или обновляется только од- на запись и удалите связь между таблицами Student и StudGroup (внешний ключ). 2.3. а) Добавить в таблицу StudGroup новый столбец с количеством студентов в группе. Создать триггер, который при добавлении в таблицу Student ново- го студента автоматически вычисляет общее число студентов в той группе, в которую был зачислен этот студент. Данное значение фиксируется в но- вом столбце таблицы StudGroup. Предварительно проверить, что добавляется только один студент. б) Добавить в таблицу StudGroup новый столбец с количеством студентов в группе. Создать триггер, который при удалении студента из таблицы Stu- dent автоматически уменьшает общее число студентов в той группе, из ко- торой был удален студент. Данное значение фиксируется в новом столбце таблицы StudGroup. Предварительно проверить, что удаляется только один студент. в) Добавить в таблицу StudGroup новый столбец с количеством студентов в группе. Создать триггер, который в таблице Student при переводе студента в новую группу автоматически увеличивает число студентов в той группе, в которую был переведен этот студент. Данное значение фиксируется в новом столбце таблицы StudGroup. А также триггер должен уменьшить общее число студентов в той группе, из которой студент был переведен, и зафиксировать его. Предварительно проверить, что переводится только один студент. 2.4**. а) Создать триггер, который при добавлении записи в таблицу Exam ав- томатически вычисляет общее число студентов, сдавших этот же экзамен в этой же группе. Данное значение фиксируется в другой таблице Ex- am_Group (Группа, Предмет, Кол-во студентов), если строка с такой груп- пой и таким предметом существует, или создается новая запись с соответ- ствующими значениями. Предварительно проверить, что добавляется только одна запись. 9 б) Создать триггер, который при удалении записи из таблицы Exam авто- матически уменьшает общее число студентов, сдавших тот же экзамен в той же группе, что и удаленный студент. Данное значение фиксируется в другой таблице Exam_Group (Группа, Предмет, Кол-во студентов). Если количество студентов становится равным нулю, то соответствующая стро- ка в таблице Exam_Group удаляется. Предварительно проверить, что удаляется (изменяется) только одна за- пись. 2.5. а) Создать триггер, который запрещает изменять в таблице Exam оценку 2 (‘неудовлетворительно’) на 5 (‘отлично’). Предварительно проверить, что изменяется только одна запись. б) Создать триггер, который при получении студентом второй и более двойки в таблице Exam выдает сообщение вида ‘Студент Иванов А.А. из группы А-13-10 – кандидат на отчисление’. Предварительно проверить, что добавляется только одна запись. 2.6. а) Создать триггер, который при добавлении записи в таблицу Exam авто- матически вычисляет средний балл того студента, который сдал данный экзамен. Данное значение фиксируется в другой таблице Рейтинг, если строка с таким студентом существует, или создается новая запись с соот- ветствующими значениями. Предварительно проверить, что добавляется только одна запись. б) Создать триггер, который при удалении или изменении записи из табли- цы Exam автоматически пересчитывает средний балл студента, который фиксируется в другой таблице Рейтинг. Если средний балл становится равным нулю, то соответствующая строка в таблице Рейтинг удаляется. Предварительно проверить, что удаляется (изменяется) только одна за- пись. в)** Создать триггер, который при добавлении или изменении записи в таблицу Exam автоматически определяет худший результат того студента, который сдал данный экзамен. Словесное описание этого результата (от- лично, с четверками, удовлетворительно, с двойками) фиксируется в дру- гой таблице РезультатыСессии, если строка с таким студентом существует, или создается новая запись с соответствующими значениями. Предварительно проверить, что добавляется (изменяется) только одна запись. г)** Создать триггер, который при удалении записи из таблицы Exam ав- томатически пересчитывает худший результат студента, который в словес- ной форме фиксируется в другой таблице РезультатыСессии. Если удале- ны все экзамены какого-либо студента, то соответствующая ему строка в таблице РезультатыСессии удаляется. Предварительно проверить, что удаляется только одна запись. 10 2.7**. а) Создать триггер, который при изменении стипендии студента в табли- це Student автоматически добавляет соответствующую запись в Журнал стипендий с полями Код студента, Старая стипендия, Новая стипендия, Дата изменения. Реализуйте в триггере возможность множественного изменения стипен- дии с учетом того, что часть студентов сохранила свою старую стипендию. б) Создать триггер, который при переводе студента в таблице Student ав- томатически добавляет соответствующую запись в Журнал переводов с полями Код студента, Старая группа, Новая группа, Дата перевода. Реализуйте в триггере возможность множественного перевода студен- тов с учетом того, что часть студентов после перевода остались в своей же группе. 2.8**. а) Создать триггер, который запрещает увеличение стипендии студенту более чем на 5% от предыдущей стипендии. Если стипендия изменилась более указанного значения, то внести поправку в данные – увеличить сти- пендию только на 5% по сравнению с предыдущим значением стипендии студента. Триггер не выполняет данную проверку в том случае, если сти- пендия ранее отсутствовала, либо студент лишается стипендии (значение NULL). Предварительно проверить, что изменяется: а) только одна запись; б) размер стипендии (иначе триггер не выполняет проверок). б) Создать триггер, который запрещает уменьшение стипендии студенту более чем на 10% от предыдущей стипендии. Если стипендия изменилась более указанного значения, то внести поправку в данные – уменьшить сти- пендию только на 10% по сравнению с предыдущим значением. Триггер не выполняет данную проверку в том случае, если стипендия ранее отсут- ствовала, либо студент лишается стипендии (значение NULL). Предварительно проверить, что изменяется: а) только одна запись; б) именно размер стипендии (иначе триггер не выполняет проверок). 2.9**. Создать триггер, который при переводе студента в другую группу авто- матически проверяет, что разница между курсами составляет не больше 1 года. Предварительно проверить, что изменяется: а) только одна запись; б) именно номер группы (иначе триггер не выполняет проверок). 2.10**. а) Создать триггер, который при добавлении нового экзамена автомати- чески проверяет дату сдачи экзамена на корректность: − она не должна превышать текущую дату; − студент не может сдавать несколько экзаменов в один и тот же день Предварительно проверить, что добавляется только одна запись. 11 б) Создать триггер, который при изменении существующего экзамена ав- томатически проверяет дату сдачи экзамена на корректность: − она не должна превышать текущую дату; − студент не может сдавать несколько экзаменов в один и тот же день Предварительно проверить, что изменяется только одна запись. Задание 3. По результатам выполнения задания 2 сформируйте отчет, со- держащий: 1. Условия заданий по созданию триггера. 2. Программный код создания триггера с необходимыми комментариями. 3. Исходные данные, необходимые для выполнения задания, и состояние таблицы после срабатывания триггера. 4. Результаты выполнения задания в виде скриншотов работы SQL Server Management Studio при разных вариантах работы триггера. |