Полякова Л.П. Основы SQL. Курс лекций
Подождите немного. Документ загружается.
3.
Даны таблицы Город и Разговор. Создать триггер,
обрабатывающий изменение продолжительности одного
телефонного разговора. При этом в таблице Город должна
измениться величина общей продолжительности связи
(всех разговоров) с соответствующим городом.
CREATE TRIGGER trig_upd
ON Разговор FOR UPDATE
AS
Выберите операторы для продолжения текста триггера.
DECLARE @c_i INT,@c_d INT,
@d_i INT,@d_d INT
SELECT @c_i=КодГорода, @d_i=продолжительность
FROM inserted
SELECT @c_d=КодГорода, @d_d=продолжительность
FROM deleted
UPDATE Город SET длит_связи=длит_связи-@d_d
WHERE КодГорода=@c_d
UPDATE Город SET длит_связи=длит_связи+@d_i
WHERE КодГорода=@c_i
UPDATE Город SET длит_связи=длит_связи-
deleted.продолжительность
WHERE КодГорода=deleted.КодГорода
UPDATE Город SET длит_связи=длит_связи+
inserted.продолжительность
WHERE КодГорода=inserted.КодГорода
UPDATE Город SET длит_связи=длит_связи-
(SELECT продолжительность FROM deleted)
WHERE КодГорода=deleted.КодГорода
UPDATE Город SET длит_связи=длит_связи+
(SELECT продолжительность FROM inserted)
WHERE КодГорода=inserted.КодГорода
UPDATE Город SET длит_связи=длит_связи-
(SELECT продолжительность FROM deleted
WHERE КодГорода=deleted.КодГорода)
UPDATE Город SET длит_связи=длит_связи+
(SELECT продолжительность FROM inserted
WHERE КодГорода= inserted.КодГорода)
341
Лекция 14 Триггеры: создание и применение
4. Дана таблица Разговор. Разработать триггер, который
выполняется вместо изменения продолжительности одного
разговора. Выполнить изменение продолжительности только
в том случае, если общая продолжительность телефонных
соединений абонента, в разговоре которого меняется
продолжительность, не превышает 300 мин.
CREATE TRIGGER trig_upd
ON Разговор INSTEAD OF UPDATE
AS
Выберите операторы для продолжения текста триггера.
DECLARE @f VARCHAR(20),
@d INT,@sd INT,
@k INT
SELECT @k=КодРазговора,@f=фамилия, @d=продолжительность
FROM inserted
SELECT @sd=SUM(продолжительность)
FROM Разговор
GROUP BY Фамилия
HAVING фамилия=@f
IF @sd>300
UPDATE Разговор SET продолжительность=@d
WHERE КодРазговора=@k
IF (SELECT SUM(продолжительность)
FROM Разговор, inserted GROUP BY Фамилия
HAVING фамилия=inserted.фамилия) >300
UPDATE Разговор SET продолжительность=
(SELECT продолжительность FROM inserted)
WHERE КодРазговора=(SELECT КодРазговора FROM inserted)
IF SUM(inserted.продолжительность
WHERE фамилия=inserted.фамилия )>300
UPDATE Разговор SET продолжительность=
inserted.продолжительность
WHERE КодРазговора=inserted.КодРазговора
IF (SELECT SUM(продолжительность) FROM Разговор) >300
UPDATE Разговор SET продолжительность=
(SELECT продолжительность FROM inserted)
WHERE КодРазговора=(SELECT КодРазговора FROM inserted)
Курс
342
Основы SQL
Лекция 15. Триггеры в рекурсивных структурах
Дается определение рекурсивной структуры в реляционной базе данных.
Приводится пример создания таблицы, реализующей рекурсивную иерархию.
Определяются правила целостности, непротиворечивости и достоверности
данных в таблицах с рекурсивными связями. Описывается программирование
триггеров, поддерживающих целостность данных в рекурсивных структурах.
Ключевые слова: рекурсивная иерархия, отношение подчиненности,
рекурсивная связь, целостность информации, триггер для добавления
записи, триггер для изменения записи, триггер для удаления записи.
Введение в рекурсивные структуры
Рассмотрим создание таблицы, реализующей рекурсивную иерархию,
на примере данных, описывающих отношения подчиненности между сот-
рудниками. В таблице emp_mgr необходимо задать как имя сотрудника
(emp), так и имя его начальника (mgr). Для рекурсивной связи одна и та
же сущность является и родительской, и дочерней. При задании рекур-
сивной связи атрибут первичного ключа мигрирует в качестве внешнего
ключа в состав неключевых атрибутов той же сущности (атрибуты emp –
сотрудник и mgr – начальник таблицы emp_mgr). Информация о руко-
водителе содержится в той же сущности, поскольку руководитель – со-
трудник той же организации. Связь руководит/подчиняется (fk_emp)
позволяет хранить древовидную иерархию подчиненности. Такой вид ре-
курсивной связи называется иерархической рекурсией и задает связь, ко-
гда руководитель (экземпляр родительской сущности) может иметь мно-
жество подчиненных (экземпляров дочерней сущности), но подчиненный
– только одного руководителя. В среде MS SQL Server создадим таблицу
emp_mgr:
CREATE TABLE emp_mgr
(emp CHAR(2) PRIMARY KEY,
mgr CHAR(2) NULL,
NoOfReports INT DEFAULT 0,
CONSTRAINT fk_emp FOREIGN KEY (mgr)
REFERENCES emp_mgr (emp) )
В таблицу введено поле NoOfReports, в котором для каждого сот-
рудника определено количество его подчиненных.
343
Лекция 15 Триггеры в рекурсивных структурах
Для удобства иллюстрации в качестве имени сотрудника и его на-
чальника будут использоваться латинские буквы. Например, ввод данных
в таблицу осуществляется операторами:
INSERT INTO emp_mgr(emp,mgr) VALUES('a',NULL)
INSERT INTO emp_mgr(emp,mgr) VALUES('b','a')
INSERT INTO emp_mgr(emp,mgr) VALUES('c','a')
INSERT INTO emp_mgr(emp,mgr) VALUES('d','a')
INSERT INTO emp_mgr(emp,mgr) VALUES('e','b')
INSERT INTO emp_mgr(emp,mgr) VALUES('f','b')
INSERT INTO emp_mgr(emp,mgr) VALUES('g','b')
INSERT INTO emp_mgr(emp,mgr) VALUES('i','c')
INSERT INTO emp_mgr(emp,mgr) VALUES('k','d')
После ввода данных в таблицу emp_mgr оператор SELECT * FROM
emp_mgr возвращает следующий результат:
emp mgr NoOfReports
-------------------------
a NULL 3
b a 3
c a 1
d a 1
e b 0
f b 0
g b 0
i c 0
k d 0
Реализация правил целостности данных
Целостность, непротиворечивость и достоверность информации в таб-
лицах с рекурсивными связями обеспечиваются выполнением ряда правил:
Правило 1. Каждый сотрудник имеет только одного руководителя.
Правило 2. Каждый сотрудник не является сам себе руководителем.
Правило 3. Каждый руководитель в первую очередь сотрудник.
Правило 4. Имеется только один сотрудник (директор организации),
который никому не подчиняется.
Правило 5. Правило 2 необходимо усилить. Каждый сотрудник не
должен находиться в роли собственного руководителя не
только непосредственно, но и опосредствованно, через
других сотрудников.
Курс
344
Основы SQL
Выполнение Правила 1 обеспечивается ограничением первичного
ключа и не требует дополнительных SQL-операторов.
Рассмотрим Правило 2. Имена сотрудника и его начальника в одной
записи не должны совпадать. При добавлении и изменении записи в таб-
лице emp_mgr это требование предъявляется к новой записи, которая до
подтверждения транзакции располагается во временной таблице с име-
нем inserted. Этому правилу соответствуют следующие SQL-операторы:
IF EXISTS (SELECT * FROM inserted WHERE mgr=emp)
BEGIN
ROLLBACK TRAN
RAISERROR('САМ СЕБЕ НАЧАЛЬНИК',16,10)
RETURN
END
Правило 3 говорит о том, что именем начальника может быть только
уже внесенное в таблицу имя сотрудника. Это требование представляет
собой декларативную ссылочную целостность и обеспечивается ограни-
чением внешнего ключа. Однако, чтобы запустить механизм триггеров,
придется удалить ограничение внешнего ключа и его функцию возложить
на триггер.
В новой или измененной записи имя начальника должно быть ука-
зано и уже присутствовать в таблице в качестве имени сотрудника, что
может быть записано следующими SQL-операторами:
IF EXISTS(SELECT * FROM inserted
WHERE mgr IS NOT NULL) AND
NOT EXISTS(SELECT * FROM inserted,emp_mgr
WHERE emp_mgr.emp=inserted.mgr)
BEGIN
RAISERROR('НЕТ НАЧАЛЬНИКА',16,10)
ROLLBACK TRAN
RETURN
END
или (что эквивалентно)
IF NOT EXISTS(SELECT * FROM emp_mgr, inserted
WHERE emp_mgr.emp=inserted.mgr
OR inserted.mgr IS NULL)
BEGIN
RAISERROR('НЕТ НАЧАЛЬНИКА',16,10)
345
Лекция 15 Триггеры в рекурсивных структурах
ROLLBACK TRAN
RETURN
END
В соответствии с Правилом 4 необходимо проверить, введена ли за-
пись о директоре (сотруднике, у которого нет начальника). Если такая
запись уже есть, ввод нового директора запрещается с помощью следую-
щих SQL-операторов:
IF EXISTS (SELECT * FROM inserted WHERE mgr IS NULL)
AND EXISTS (SELECT * FROM emp_mgr,inserted
WHERE emp_mgr.mgr IS NULL
AND emp_mgr.emp<>inserted.emp)
BEGIN
ROLLBACK TRAN
RAISERROR('ОДИН ДИРЕКТОР УЖЕ ЕСТЬ',16,10)
RETURN
END
Оператор UPDATE может изменить иерархическую структуру таким
образом, что возникает ситуация, когда сотрудник становится начальни-
ком самому себе через других сотрудников, т.е. в иерархии подчиненности
возникает петля. Для исключения подобных преобразований используем
SQL-операторы:
IF UPDATE(mgr)--изменился начальник
BEGIN
DECLARE @x CHAR(2), @y CHAR(2),
@xx CHAR(2) --узнали имя сотрудника,
у которого изменился начальник
SELECT @xx=inserted.emp FROM inserted
SELECT @x=@xx
SELECT @y='*'
WHILE @y IS NOT NULL --пока не дошли до директора
BEGIN
--запомнили имя начальника
SELECT @y=mgr FROM emp_mgr
WHERE emp=@x
IF @xx=@y
--имя сотрудника и его начальника совпали
BEGIN
RAISERROR('транзитивное замыкание',16,10)
Курс
346
Основы SQL
ROLLBACK TRAN
RETURN
END
ELSE
--далее начальник становится сотрудником,
--и в цикле будем искать его начальника
SELECT @x=@y
END
END
Чтобы сработали триггеры, необходимо удалить ограничение внеш-
него ключа:
ALTER TABLE emp_mgr DROP CONSTRAINT fk_emp
Ниже приведен текст триггеров, поддерживающих целостность дан-
ных в иерархических структурах. Предполагается, что триггеры обрабаты-
вают ввод, изменение или удаление одной записи.
Добавление записи в рекурсивную структуру
Пример 15.1. Триггер для добавления записи в таблицу.
ALTER TRIGGER emp_ins
ON emp_mgr FOR INSERT
AS
--Правило 2
IF EXISTS (SELECT * FROM inserted WHERE mgr=emp)
BEGIN
ROLLBACK TRAN
RAISERROR('САМ СЕБЕ НАЧАЛЬНИК',16,10)
RETURN
END
--Правило 4
IF EXISTS (SELECT * FROM inserted WHERE mgr IS
NULL) AND
EXISTS (SELECT * FROM emp_mgr,inserted WHERE
emp_mgr.mgr IS NULL AND emp_mgr.emp<>inserted.emp)
BEGIN
ROLLBACK TRAN
RAISERROR('ОДИН ДИРЕКТОР УЖЕ ЕСТЬ',16,10)
RETURN
347
Лекция 15 Триггеры в рекурсивных структурах
END
--Правило 3
IF EXISTS(SELECT * FROM inserted
WHERE mgr IS NOT NULL) AND
NOT EXISTS(SELECT * FROM inserted,emp_mgr
WHERE emp_mgr.emp=inserted.mgr)
BEGIN
RAISERROR('НЕТ ТАКОГО НАЧАЛЬНИКА',16,10)
ROLLBACK TRAN
RETURN
END
--Пересчет числа подчиненных у начальника
добавленного подчиненного
DECLARE @e CHAR(2), @m CHAR(2)
SELECT @e=emp_mgr.emp FROM emp_mgr, inserted
WHERE emp_mgr.emp=inserted.mgr
UPDATE emp_mgr
SET emp_mgr.NoOfReports= emp_mgr.NoOfReports+1
WHERE emp_mgr.emp=@e
Изменение записи в рекурсивной структуре
Пример 15.2. Триггер для изменения записи в таблице.
CREATE TRIGGER emp_upd ON emp_mgr
FOR UPDATE
AS
IF UPDATE(mgr)
BEGIN
--Правило 5
DECLARE @x CHAR(2), @y CHAR(2), @xx CHAR(2)
SELECT @xx=inserted.emp FROM inserted
SELECT @x=@xx
SELECT @y='*'
WHILE @y IS NOT NULL
BEGIN
SELECT @y=mgr FROM emp_mgr WHERE emp=@x
IF @xx=@y
BEGIN
RAISERROR('транзитивное замыкание',16,10)
ROLLBACK TRAN
RETURN
Курс
348
Основы SQL
END
ELSE
SELECT @x=@y
END
END
--Правило 2
IF EXISTS (SELECT * FROM inserted WHERE mgr=emp)
BEGIN
ROLLBACK TRAN
RAISERROR('САМ СЕБЕ НАЧАЛЬНИК',16,10)
RETURN
END
--Правило 4
IF EXISTS (SELECT * FROM inserted WHERE mgr
IS NULL) AND EXISTS (SELECT *
FROM emp_mgr,inserted WHERE emp_mgr.mgr IS NULL
AND emp_mgr.emp<>inserted.emp)
BEGIN
ROLLBACK TRAN
RAISERROR('ОДИН ДИРЕКТОР УЖЕ ЕСТЬ',16,10)
RETURN
END
--Правило 3
IF UPDATE(mgr)
IF NOT EXISTS(SELECT * FROM emp_mgr, inserted
WHERE emp_mgr.emp=inserted.mgr
OR inserted.mgr IS NULL)
BEGIN
RAISERROR('НЕТ ТАКОГО НАЧАЛЬНИКА',16,10)
ROLLBACK TRAN
RETURN
END
IF UPDATE(mgr)
--пересчет числа подчиненных у старого и нового
начальников
BEGIN
UPDATE emp_mgr
SET emp_mgr.NoOfReports=emp_mgr.NoOfReports+1
FROM inserted WHERE emp_mgr.emp=inserted.mgr
UPDATE emp_mgr
SET emp_mgr.NoOfReports=emp_mgr.NoOfReports-1
FROM deleted WHERE emp_mgr.emp=deleted.mgr
349
Лекция 15 Триггеры в рекурсивных структурах
END
IF UPDATE(emp)
--если изменилось имя сотрудника, следует изменить
имя начальника
--у всех его подчиненных
UPDATE emp_mgr SET emp_mgr.mgr=inserted.emp
FROM emp_mgr, inserted, deleted WHERE
emp_mgr.mgr=deleted.emp
Попытка подчинить сотрудника с именем ‘b’ начальнику с именем
‘e’ будет сервером отвергнута, иначе в организации сложилась бы такая
ситуация: сотрудник ‘e’ подчинятся сотруднику ‘b’, а сотрудник ‘b’
подчиняется сотруднику ‘e’.
UPDATE emp_mgr SET mgr='e' WHERE emp='b'
Server: Msg 50000, Level 16, State 10,
Procedure emp_upd, Line 15 транзитивное замыкание
Выполнение команды
UPDATE emp_mgr SET mgr='f' WHERE emp='e'
и команды
UPDATE emp_mgr SET mgr='a' WHERE emp='g'
приведет к следующему изменению первоначальной иерархической стру-
ктуры:
emp mgr NoOfReports
-------------------------
a NULL 4
b a 1
c a 1
d a 1
e f 0
f b 1
g a 0
i c 0
k d 0
Курс
350
Основы SQL