Триггеры в рекурсивных структурах

1. Каждый сотрудник имеет только одного руководителя.

2. Каждый сотрудник не является сам себе руководителем.

3. Каждый руководитель в первую очередь сотрудник.

4. Имеется только один сотрудник (директор организации), который никому не подчиняется.

5. Правило 2 необходимо усилить. Каждый сотрудник не должен находиться в роли собственного руководителя не только непосредственно, но и опосредствованно, через других сотрудников.

Выполнение правила 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)

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)

ROLLBACK TRAN

RETURN

END

ELSE

--далее начальник становится сотрудником,

--и в цикле будем искать его начальника

SELECT @x=@y

END

END

Чтобы сработали триггеры, необходимо удалить ограничение внешнего ключа:

ALTER TABLE emp_mgr DROP CONSTRAINT fk_emp

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

Добавление записи в рекурсивную структуру

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

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.1. Триггер для добавления записи в таблицу.

Изменение записи в рекурсивной структуре

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

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

END

IF UPDATE(emp)

--если изменилось имя сотрудника, следует изменить

--имя начальника у всех его подчиненных

UPDATE emp_mgr SET emp_mgr.mgr=inserted.emp

FROM emp_mgr, inserted, deleted WHERE

emp_mgr.mgr=deleted.emp

Пример 15.2. Триггер для изменения записи в таблице.

Попытка подчинить сотрудника с именем `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

Удаление записи из рекурсивной структуры

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)

RETURN

END

Пример 15.3. Триггер для удаления записи из таблицы.

Попытка удаления записи о директоре будет отвергнута сервером:

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

Размещено на Allbest.ru