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

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