Полякова Л.П. Основы SQL. Курс лекций
Подождите немного. Документ загружается.
Удаление записи из рекурсивной структуры
Пример 15.3. Триггер для удаления записи из таблицы.
ALTER TRIGGER emp_del
ON emp_mgr
FOR DELETE
AS
DECLARE @e CHAR(2), @m CHAR(2), @r INT
SELECT @e=emp,@m=mgr,@r=NoOfReports FROM deleted
IF @m IS NOT NULL
--удаляется сотрудник, не являющийся директором
BEGIN
IF @r=0
-- удаляется сотрудник, у которого нет подчиненных
-- уменьшается число подчиненных у начальника
-- удаляемого сотрудника
UPDATE emp_mgr SET NoOfReports=
NoOfReports-1
WHERE emp=@m
ELSE
BEGIN
-- удаляется сотрудник, у которого есть подчиненные
-- переподчиним его подчиненных его начальнику,
т.е.
-- начальником подчиненных удаляемого сотрудника
-- становится его начальник
UPDATE emp_mgr SET NoOfReports=
NoOfReports+@r-1
WHERE emp=@m
UPDATE emp_mgr SET mgr=@m
WHERE mgr=@e
END
END
ELSE
--Правило 4
IF EXISTS(SELECT * FROM emp_mgr)
BEGIN
ROLLBACK TRAN
RAISERROR('НЕЛЬЗЯ УДАЛЯТЬ
ДИРЕКТОРА',16,10)
351
Лекция 15 Триггеры в рекурсивных структурах
RETURN
END
Попытка удаления записи о директоре будет отвергнута сервером:
DELETE FROM emp_mgr WHERE emp='a'
Server: Msg 50000, Level 16, State 10,
Procedure emp_del, Line 24
НЕЛЬЗЯ УДАЛЯТЬ ДИРЕКТОРА
В результате удаления рядового сотрудника с именем b его подчи-
ненные e, f и g станут подчиненными сотрудника с именем a.
DELETE FROM emp_mgr WHERE emp='b'
Первоначальное содержимое таблицы emp_mgr изменится следую-
щим образом:
emp mgr NoOfReports
-------------------------
a NULL 5
c a 1
d a 1
e a 0
f a 0
g a 0
i c 0
k d 0
В таблице Граф представлен график сетевого планирования:
Курс
352
Основы SQL
CREATE TABLE Граф
(номер INT PRIMARY KEY, --номер работы
нач_соб INT, --начальное событие
кон_соб INT, --конечное событие
длит INT) --длительность работы
Таблица 15.1
В таблице, моделирующей сетевой график, каждая запись соответст-
вует некоторой работе, связывающей два события: начальное и конечное.
Например:
Реализуем в виде триггеров ряд правил для обеспечения целостности
данных в подобной сетевой структуре. Начальное событие графика (ис-
ток) обозначено цифрой «1».
353
Лекция 15 Триггеры в рекурсивных структурах
Номер Нач_соб Кон_соб Длит
1125
21412
3133
4346
5452
6358
7265
8564
9577
10 6 7 2
Таблица 15.2
Вариант 1
1. Разработать триггер для вставки новой работы, причем,
когда начальное событие не равно «1», необходимо
отменить ввод, если конечное событие новой работы еще
не было указано как начальное ранее введенной работы.
CREATE TRIGGER trig_ins
ON Граф FOR INSERT
AS
Выберите операторы для продолжения текста триггера.
IF NOT EXISTS(SELECT * FROM inserted
WHERE нач_соб=1)
IF NOT EXISTS(SELECT * FROM Граф
WHERE кон_соб=ALL(SELECT нач_соб
FROM inserted))
ROLLBACK TRAN
IF NOT EXISTS(SELECT * FROM inserted
WHERE нач_соб=1) AND
NOT EXISTS(SELECT * FROM Граф
WHERE кон_соб=ALL(SELECT нач_соб
FROM inserted))
ROLLBACK TRAN
IF NOT EXISTS(SELECT * FROM inserted
WHERE нач_соб=1))
IF NOT EXISTS(SELECT * FROM Граф
WHERE кон_соб=(SELECT нач_соб
FROM inserted)
ROLLBACK TRAN
IF NOT EXISTS(SELECT * FROM inserted
WHERE нач_соб=1) AND
NOT EXISTS(SELECT * FROM Граф
WHERE кон_соб=inserted.нач_соб)
`ROLLBACK TRAN
Курс
354
Основы SQL
2. Разработать триггер для удаления работы, причем
необходимо отменить удаление, если только эта работа и
никакая другая приводит к данному конечному событию.
CREATE TRIGGER trig_del
ON Граф FOR DELETE
AS
Выберите операторы для продолжения текста триггера.
DECLARE @e INT,@n INT
SELECT @n=номер, @e=кон_соб FROM deleted
IF NOT EXISTS(SELECT *
FROM Граф
WHERE номер<>@n AND кон_соб=@e )
ROLLBACK TRAN
DECLARE @e INT,@n INT
SELECT @n=номер, @e=кон_соб FROM deleted
IF EXISTS(SELECT *
FROM Граф
WHERE кон_соб<>@e )
ROLLBACK TRAN
IF NOT EXISTS(SELECT *
FROM Граф
WHERE номер<>deleted.номер
AND кон_соб=deleted.кон_соб )
ROLLBACK TRAN
F NOT EXISTS(SELECT *
FROM Граф
WHERE номер<> (SELECT номер
FROM deleted)
AND кон_соб=(SELECT кон_соб
FROM deleted))
ROLLBACK TRAN
355
Лекция 15 Триггеры в рекурсивных структурах
3. Запретить одновременное изменение только номеров
начального и конечного событий.
CREATE TRIGGER trig_upd
ON Граф FOR UPDATE
AS
Выберите операторы для продолжения текста триггера.
IF (COLUMNS_UPDATED() & 6)=6
ROLLBACK TRAN
IF UPDATE(нач_соб) AND UPDATE(кон_соб)
ROLLBACK TRAN
IF UPDATE(нач_соб,кон_соб)
ROLLBACK TRAN
IF (COLUMNS_UPDATED() & 2)=2
ROLLBACK TRAN
Курс
356
Основы SQL
Вариант 2
1. Разработать триггер для вставки новой работы, причем
необходимо отменить ввод, если номер конечного
события оказался меньше номера начального события.
CREATE TRIGGER trig_ins
ON Граф FOR INSERT
AS
Выберите операторы для продолжения текста триггера.
IF EXISTS(SELECT * FROM inserted
WHERE нач_соб>кон_соб)
ROLLBACK TRAN
IF (SELECT * FROM inserted
WHERE нач_соб>кон_соб)
ROLLBACK TRAN
IF inserted.нач_соб>inserted.кон_соб)
ROLLBACK TRAN
IF NOT EXISTS(SELECT * FROM inserted
WHERE нач_соб<=кон_соб)
ROLLBACK TRAN
357
Лекция 15 Триггеры в рекурсивных структурах
2. Разработать триггер для удаления работы, причем
необходимо отменить удаление, если работа оказалась
единственной, выходящей из данного события.
CREATE TRIGGER trig_del
ON Граф FOR DELETE
AS
Выберите операторы для продолжения текста триггера.
DECLARE @e INT,@n INT
SELECT @n=номер, @e=нач_соб FROM deleted
IF NOT EXISTS(SELECT *
FROM Граф
WHERE номер<>@n AND нач_соб=@e )
ROLLBACK TRAN
DECLARE @e INT,@n INT
SELECT @n=номер, @e=нач_соб FROM deleted
IF EXISTS(SELECT *
FROM Граф
WHERE номер<>@n AND нач_соб<>@e )
ROLLBACK TRAN
IF NOT EXISTS(SELECT *
FROM Граф
WHERE номер<>deleted.номер
AND нач_соб=deleted.нач_соб )
ROLLBACK TRAN
IF NOT EXISTS(SELECT *
FROM Граф
WHERE номер<> (SELECT номер
FROM deleted)
AND нач_соб=(SELECT нач_соб FROM deleted))
ROLLBACK TRAN
Курс
358
Основы SQL
3. Запретить одновременное изменение номеров начального
и конечного событий и длительности.
CREATE TRIGGER trig_upd
ON Граф FOR UPDATE
AS
Выберите операторы для продолжения текста триггера.
F (COLUMNS_UPDATED() & 14)=14
ROLLBACK TRAN
F UPDATE(нач_соб) AND UPDATE(кон_соб) AND
UPDATE(длит)
ROLLBACK TRAN
IF UPDATE(нач_соб AND кон_соб AND длит)
ROLLBACK TRAN
IF (COLUMNS_UPDATED() & 15)=15
ROLLBACK TRAN
359
Лекция 15 Триггеры в рекурсивных структурах
Вариант 3
1. Разработать триггер для вставки новой работы, причем
необходимо отменить ввод, если номер начального
события оказался меньше номера хотя бы одного
конечного события, введенной ранее работы.
CREATE TRIGGER trig_ins
ON Граф FOR INSERT
AS
Выберите операторы для продолжения текста триггера.
DECLARE @e INT
SELECT @e=нач_соб FROM inserted
IF EXISTS(SELECT * FROM Граф WHERE кон_соб>@e)
ROLLBACK TRAN
IF EXISTS(SELECT * FROM Граф
WHERE кон_соб>ANY(SELECT @e=нач_соб
FROM inserted)
ROLLBACK TRAN
IF EXISTS(SELECT * FROM Граф
WHERE кон_соб>(SELECT @e=нач_соб
FROM inserted)
ROLLBACK TRAN
DECLARE @e INT
SELECT @e=нач_соб FROM inserted
IF NOT EXISTS(SELECT * FROM Граф
WHERE кон_соб<@e
ROLLBACK TRAN
Курс
360
Основы SQL