Архив / Пособие_Триггеры и процедуры (MS SQL Server)
.pdf
ПетрГУ, кафедра прикладной математики и кибернетики
Рассмотрим более детально работу триггера.
Пусть таблица tblStudent включает следующие записи: tblStudent
StudentId |
StudentName |
Grant |
… |
1023 |
Бобров А.А. |
10000 |
… |
1025 |
Мухина В.Н. |
12000 |
… |
1026 |
Лобов А.Г. |
14000 |
… |
Пусть пользователь отправил на выполнение следующую команду:
Update tblStudent Set Grant = 12000 Where StudentId = 1023
СУБД выполнит эту команду и внесет изменения в таблицу tblStudent: tblStudent
StudentId |
StudentName |
Grant |
… |
1023 |
Бобров А.А. |
12000 |
… |
1025 |
Мухина В.Н. |
12000 |
… |
1026 |
Лобов А.Г. |
14000 |
… |
Затем СУБД вызовет триггер tgrStudentGrantUpdate.
При этом СУБД создаст временные таблицы Inserted и Deleted и наполнит их следующими данными:
Deleted
|
StudentId |
StudentName |
Grant |
… |
|
1023 |
Бобров А.А. |
10000 |
… |
Inserted |
|
|
|
|
|
|
|
|
|
|
StudentId |
StudentName |
Grant |
… |
|
1023 |
Бобров А.А. |
12000 |
… |
В теле триггера вызываются две команды Select, которые записывают значения в три переменных:
–переменная @Grant_old получает значение «10000»
–переменная @Grant_new получает значение «12000»
–переменная @Id получает значение «1023».
Проверка условия дает истинное значение, и триггер вызывает команду
UPDATE tblStudent SET Grant = 1.05 * 10000
WHERE StudentId = 1023
Эта команда является командой обновления данных, поэтому СУБД для нее так же проверит наличие триггера, а это тот же самый триггер tgrStudentGrantUpdate, который будет вызван повторно, но уже с другими данными.
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
11 |
ПетрГУ, кафедра прикладной математики и кибернетики
Для второго вызова триггера СУБД создает новые временные таблицы Inserted и Deleted и наполняет их следующими данными:
Deleted
|
StudentId |
StudentName |
Grant |
… |
|
1023 |
Бобров А.А. |
12000 |
… |
Inserted |
|
|
|
|
|
|
|
|
|
|
StudentId |
StudentName |
Grant |
… |
|
1023 |
Бобров А.А. |
10500 |
… |
В теле триггера будут вызваны две команды Select, которые запишут значения в три переменных:
–переменная @Grant_old получает значение «12000»
–переменная @Grant_new получает значение «10500»
–переменная @Id получает значение «1023».
Проверка условия даст значение ложь, и триггер на этом закончит свою работу. Далее произойдет возврат в тело триггера, вызванного первым, и он тоже закончит свою работу.
В результате в таблице tblStudent записи будут такими: tblStudent
StudentId |
StudentName |
Grant |
… |
1023 |
Бобров А.А. |
10500 |
… |
1025 |
Мухина В.Н. |
12000 |
… |
1026 |
Лобов А.Г. |
14000 |
… |
Первая запись будет такой, какой она была в таблице Inserted при втором вызове триггера, так как после этого никаких изменений в таблицу tblStudent внесено не было.
Замечание
Так как триггер вызывается после выполнения операции, то все изменения, которые должна была сделать операция, уже внесены в таблицу, поэтому единственный способ что-то исправить заключается во внесении новых изменений в эту же самую таблицу. Это изменение привет к повторному вызову триггера. Зацикливания вызовов триггера не произойдет, так как триггер делает изменения в таблице только после проверки условия, которое при втором вызове триггера будет ложным.
Рассмотрим другую ситуацию.
Пусть таблица tblStudent включает следующие записи: tblStudent
|
StudentId |
StudentName |
Grant |
|
… |
|
|
1023 |
Бобров А.А. |
10000 |
|
… |
|
|
1025 |
Мухина В.Н. |
12000 |
|
… |
|
|
1026 |
Лобов А.Г. |
14000 |
|
… |
|
|
|
|
|
|
|
|
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
12 |
|||||
ПетрГУ, кафедра прикладной математики и кибернетики
Пусть пользователь отправил на выполнение следующую команду:
Update tblStudent Set Grant = 18000
Эта команда отличается от предыдущей тем, что в результате ее выполнения изменению подвергнутся все три записи таблицы tblStudent. Как же сработает триггер в этой ситуации?
Как уже говорилось ранее, триггер будет вызван после того, как будут изменены все записи, т. е. таблица tblStudent станет такой:
tblStudent
StudentId |
StudentName |
Grant |
… |
1023 |
Бобров А.А. |
18000 |
… |
1025 |
Мухина В.Н. |
18000 |
… |
1026 |
Лобов А.Г. |
18000 |
… |
Затем СУБД вызовет триггер tgrStudentGrantUpdate.
При этом СУБД создаст временные таблицы Inserted и Deleted и наполнит их следующими данными:
Deleted
|
StudentId |
StudentName |
Grant |
… |
|
1023 |
Бобров А.А. |
10000 |
… |
|
1025 |
Мухина В.Н. |
12000 |
… |
|
1026 |
Лобов А.Г. |
14000 |
… |
Inserted |
|
|
|
|
|
|
|
|
|
|
StudentId |
StudentName |
Grant |
… |
|
1023 |
Бобров А.А. |
18000 |
… |
|
1025 |
Мухина В.Н. |
18000 |
… |
|
1026 |
Лобов А.Г. |
18000 |
… |
В теле триггера будут вызваны две команды Select, которые должны записать значения в три переменных: @Grant_old, @Grant_new, @Id. Именно здесь и возникает проблема: какие значения получат эти переменные, если в таблицах Deleted и Inserted находятся по три записи, а в каждую переменную можно записать только одно значение? В этом случае СУБД не выдаст сообщение об ошибке, а просто запишет в переменные одно из значений. Это могут быть значения, соответствующие студенту с номером 1023, так как это первая запись в таблицах. Далее будет проверено условие о величине изменения стипендии, которое будет истинным и для первого студента стипендия будет заменена, на этом триггер закончит свою работу. Про две другие записи триггер и не вспомнит и таблица tblStudent станет такой:
tblStudent
|
StudentId |
StudentName |
Grant |
|
… |
|
|
1023 |
Бобров А.А. |
10500 |
|
… |
|
|
1025 |
Мухина В.Н. |
18000 |
|
… |
|
|
1026 |
Лобов А.Г. |
18000 |
|
… |
|
|
|
|
|
|
|
|
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
13 |
|||||
ПетрГУ, кафедра прикладной математики и кибернетики
Таким образом, триггер неправильно выполнит ограничения предметной области. Он исправит только одну запись. Для того чтобы триггер обработал каждую запись в таблицах Inserted и Deleted, необходимо использовать механизм курсора.
Удалим триггер tgrStudentGrantUpdate и создадим новый триггер tgrStudentGrantUpdateCursor.
CREATE TRIGGER tgrStudentGrantUpdateCursor ON tblStudent AFTER UPDATE
AS
DECLARE @Grant_old float, @Grant_new float, @Id int; DECLARE DelCursor CURSOR FOR Select Grant from Deleted;
DECLARE InsCursor CURSOR FOR Select Grant, StudentId from Inserted; BEGIN
OPEN DelCursor;
OPEN InsCursor;
FETCH NEXT FROM DelCursor INTO @Grant_old; FETCH NEXT FROM InsCursor INTO @Grant_new, @Id; WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Grant_new - @Grant_old > 0.05 * @Grant_old) UPDATE tblStudent SET Grant = 1.05 * @Grant_old WHERE StudentId = @Id;
FETCH NEXT FROM DelCursor INTO @Grant_old; FETCH NEXT FROM InsCursor INTO @Grant_new, @Id;
END
CLOSE DelCursor; Deallocate DelCursor; CLOSE InsCursor; Deallocate InsCursor;
END
В этом случае если таблица tblStudent включала следующие записи: tblStudent
|
StudentId |
StudentName |
Grant |
|
… |
|
|
1023 |
Бобров А.А. |
10000 |
|
… |
|
|
1025 |
Мухина В.Н. |
12000 |
|
… |
|
|
1026 |
Лобов А.Г. |
14000 |
|
… |
|
|
|
|
|
|
|
|
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
14 |
|||||
ПетрГУ, кафедра прикладной математики и кибернетики
и пользователь отправил на выполнение следующую команду:
Update tblStudent Set Grant = 18000
СУБД выполнит эту команду и таблица tblStudent станет такой: tblStudent
StudentId |
StudentName |
Grant |
… |
1023 |
Бобров А.А. |
18000 |
… |
1025 |
Мухина В.Н. |
18000 |
… |
1026 |
Лобов А.Г. |
18000 |
… |
После этого СУБД вызовет триггер tgrStudentGrantUpdateCursor, который последовательно обработает каждую запись в таблицах Deleted и Inserted. Для каждой записи проверит условие, и так как оно будет истинным для каждой записи, вызовет для каждой записи команду Update с правильным значением стипендии. При повторном вызове триггера таблицы Deleted и Inserted уже будут содержать по одной записи, для них условия будут ложными и триггер закончит свою работу. Таким образом триггер будет вызван 4 раза. Первый раз для трех записей вместе, а потом для каждой из трех записей в отдельности. Таблица tblStudent будет содержать следующие записи:
tblStudent
StudentId |
StudentName |
Grant |
… |
1023 |
Бобров А.А. |
10500 |
… |
1025 |
Мухина В.Н. |
12600 |
… |
1026 |
Лобов А.Г. |
14700 |
… |
Если для этой таблицы мы выполнит команду
Update tblStudent Set Grant = 12000
То триггер будет вызван два раза (один раз для всех трех записей и один раз для первой записи, так как для двух других записей условие будет ложным и изменения в эти записи вносить не нужно, а, следовательно, и триггер вызван не будет) и таблица tblStudent будет содержать следующие записи:
tblStudent
StudentId |
StudentName |
Grant |
… |
1023 |
Бобров А.А. |
11025 |
… |
1025 |
Мухина В.Н. |
12000 |
… |
1026 |
Лобов А.Г. |
12000 |
… |
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
15 |
ПетрГУ, кафедра прикладной математики и кибернетики
Пример 2
Ограничение предметной области: количество практик по дисциплине математического факультета должно превышать количество часов лекций.
Это условие будем проверять для операции добавления новых данных в таблицу tblSubject.
CREATE TRIGGER tgrNewSubjectInsert ON tblSubject AFTER INSERT
AS
DECLARE @idFaculty int, @HLect int, @HPract int, @FName char(100); DECLARE InsCursor CURSOR FOR Select FacultyId, LecturesHour,
PracticeHour FROM Inserted;
BEGIN
OPEN InsCursor;
FETCH NEXT FROM InsCursor INTO @idFaculty, @HLect, @HPract; WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FName= FacultyName FROM tblFaculty WHERE FacultyId=@idFaculty;
IF (@FName =’математический’) and (@HLect >= @HPract) BEGIN
RAISERROR('На математическом факультете практик должно быть больше, чем лекций',16,1);
ROLLBACK TRANSACTION; END
FETCH NEXT FROM InsCursor INTO @idFaculty, @HLect, @HPract;
END
CLOSE InsCursor; Deallocate InsCursor;
END
В триггере используется команда для вызова исключительной ситуации raiserror(<сообщение>,<уровень серьезности>,<состояние>)
<уровень серьезности> – произвольное целое число от 0 до 18 может указать любой пользователь.
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
16 |
ПетрГУ, кафедра прикладной математики и кибернетики
<состояние> – произвольное целое число от 1 до 127. Отрицательное значение состояния по умолчанию приравнивается к 1. Значение 0 и значения больше 127 приводят к формированию ошибки. Если одна и та же пользовательская ошибка возникает в нескольких местах, то при помощи уникального номера состояния для каждого местоположения можно определить, в каком месте кода появилась ошибка.
После добавления новых записей вызывается триггер, который с помощью курсора проверяет каждую добавленную в таблицу запись о предмете следующим образом:
–в таблице tblFaculty триггер по идентификатору факультета узнает название факультета, и записывает его в переменную @FName;
–далее триггер проверяет условие, что факультет математический и количество часов лекций меньше или равно количеству часов практик, если условие будет верным, то триггер вызовет исключительную ситуацию и откат транзакции, которые приведут к отмене ввода всех записей.
Курсор просматривает записи в таблице Inserted, извлекая идентификатор факультета в переменную @idFaculty, количество часов лекций – в переменную @HLect, количество часов практик – в переменную @HPract.
Например, пусть таблица tblFaculty имеет следующие записи: tblFaculty
FacultyId |
FacultyName |
DeenName |
FacultyRoomId |
FacultyPhone |
FacultyStudent |
1 |
Физико- |
Звонов А.П. |
132 |
710945 |
568 |
|
технический |
|
|
|
|
|
|
|
|
|
|
2 |
Математический |
Кашин О.И. |
243 |
713498 |
603 |
|
|
|
|
|
|
Если пользователь выполнит следующую команду:
Insert into tblSubject(SubjectName, LecturesHour, PracticeHour, FacultyId) values(‘Информатика’, 30, 51, 2), values(‘Информатика’, 30, 20, 1)
Эта команда будет успешно выполнена, так как первая добавляемая запись вносит дисциплину «Информатика» для математического факультета с количеством практик 51, а количеством лекций – 30, т.е. ограничение предметной области не нарушается, а вторая запись добавляет предмет «Информатика» для физико-технического факультета, и хотя здесь количество часов практик не превосходит количество часов лекций, но предмет будет читаться не на математическом факультете, поэтому ограничение предметной области здесь то же не нарушается.
Если пользователь попытается выполнить команду:
Insert into tblSubject(SubjectName, LecturesHour, PracticeHour, FacultyId) values(‘Алгебра’, 30, 51, 2), values(‘Физика’, 30, 20, 2)
Эта команда не будет выполнена. В таблицу tblSubject не будет добавлена ни одна запись, хотя первая добавляемая запись удовлетворяет ограничениям предметной области, но так как она добавляется в таблицу в рамках одной команды с другой записью, которая не удовлетворяет ограничениям предметной области, то обе записи не будут добавлены в таблицу.
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
17 |
ПетрГУ, кафедра прикладной математики и кибернетики
Этот же самый триггер можно было бы записать в другом виде:
CREATE TRIGGER tgrNewSubjectInsert ON tblSubject INSTEAD OF INSERT
AS
DECLARE @idFaculty int, @HLect int, @HPract int, @FName char(100), @SName char(40), @idSubject int;
DECLARE InsCursor CURSOR FOR Select SubjectId, SubjectName, LecturesHour, PracticeHour, FacultyId, FROM Inserted;
BEGIN
OPEN InsCursor;
FETCH NEXT FROM InsCursor INTO @idSubject, @SName, @HLect, @HPract, @idFaculty;
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @FName= FacultyName FROM tblFaculty WHERE FacultyId=@idFaculty;
IF (@FName =’математический’) and (@HLect < @HPract) BEGIN
INSERT INTO tblSubject(SubjectId, SubjectName, LecturesHour, PracticeHour, FacultyId) values (SubjectId, SubjectName, LecturesHour, PracticeHour, FacultyId)
END
ELSE
BEGIN
Print(‘'На математическом факультете практик должно быть больше, чем лекций’);
END
FETCH NEXT FROM InsCursor INTO @idSubject, @SName, @HLect, @HPract, @idFaculty;
END
CLOSE InsCursor; Deallocate InsCursor;
END
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
18 |
ПетрГУ, кафедра прикладной математики и кибернетики
Этот триггер вызывается вместо операции Insert, т.е. операция не выполняется СУБД, поэтому триггер после того, как проверит, что ограничение предметной области выполнено, должен добавить запись в таблицу, а если ограничение не выполнено, то не добавлять запись. В этом случае, если пользователь попытается выполнить команду
Insert into tblSubject(SubjectName, LecturesHour, PracticeHour, FacultyId) values(‘Алгебра’, 30, 51, 2), values(‘Физика’, 30, 20, 2)
Дисциплина «Алгебра» будет добавлена в таблицу tblSubject, а дисциплина «Физика»
– не будет добавлена, при этом будет выведено соответствующее сообщение.
Пример 3
Ограничение предметной области: количество студентов в одной группе не должно превышать значение 25.
Это условие будем проверять для операции добавления новых данных в таблицу tblStudent.
CREATE TRIGGER tgrNewStudentInsert ON tblStudent AFTER INSERT
AS
DECLARE @idGroup int, @GCount int;
DECLARE InsCursor CURSOR FOR Select GroupId FROM Inserted; BEGIN
OPEN InsCursor;
FETCH NEXT FROM InsCursor INTO @idGroup; WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @GCount=count(*) FROM tblStudent WHERE GroupId=@idGroup;
IF (@GCount >25) BEGIN
RAISERROR('В группе не может быть более 25 студентов',16,1); ROLLBACK TRANSACTION;
END
FETCH NEXT FROM InsCursor INTO @idGroup;
END
CLOSE InsCursor; Deallocate InsCursor;
END
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
19 |
ПетрГУ, кафедра прикладной математики и кибернетики
После добавления новых записей вызывается триггер, который с помощью курсора проверяет каждую добавленную в таблицу запись о студенте следующим образом:
–в таблице tblStudent триггер подсчитывает количество студентов, которые учатся в той же группе, в которую зачислен студент, и записывает его в переменную @GCount;
–далее триггер проверяет условие, что количество студентов в группе не превосходит значения 25, если условие будет верным, то триггер вызовет исключительную ситуацию и откат транзакции, которые приведут к отмене
ввода всех записей.
Курсор просматривает записи в таблице Inserted, извлекая идентификатор группы в переменную @idGroup.
Пример 4
Поддержка ограничения, связывающего данные двух таблиц.
Таблица tblFaculty включает столбец с именем FacultyStudent, который должен содержать суммарный количество студентов, обучающихся на факультете. Каждый студент описывается записью в таблице tblStudent. Следовательно, данные в таблицах tblFaculty и tblStudent должны быть согласованы, т.е., если в таблице tblFaculty указано, что на математическом факультете обучается 214 студентов, то в таблице tblStudent должно быть ровно 214 записей с информацией о 214-ти студентах математического факультета, не считая записи для других факультетов. И если в таблицу tblStudent добавляется новый студент, или удаляется информация о студенте, то соответственно должно измениться значение в таблице tblFaculty. Для автоматического согласования данных в двух таблицах создадим два триггера для таблицы tblStudent.
CREATE TRIGGER tgrStudentInsert
ON tblStudent AFTER INSERT, UPDATE
AS
DECLARE @idFaculty int, @TotalSum int, @idGroup int;
DECLARE InsCursor CURSOR FOR Select GroupId FROM Inserted; BEGIN
OPEN InsCursor;
FETCH NEXT FROM InsCursor INTO @idGroup; WHILE @@FETCH_STATUS = 0
BEGIN
Select @idFaculty=FacultyId From tblGroup Where GroupId=@idGroup; EXECUTE prStudentsOfFaculty @idFaculty, @TotalSum OUTPUT; UPDATE tblFaculty SET FacultyStudent=@TotalSum WHERE
FacultyId=@idFaculty;
FETCH NEXT FROM InsCursor INTO @idGroup; END;
CLOSE InsCursor; Deallocate InsCursor;
END
Конспект лекций по дисциплине «Базы данных» (Процедуры и триггеры) |
20 |