Реализация правил целостности данных

Целостность, непротиворечивость и достоверность информации в таблицах с рекурсивными связями обеспечиваются выполнением ряда правил:

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

Выполнение правила 1 обеспечивается ограничением первичного ключа и не требует дополнительных SQL-операторов.

Рассмотрим правило 2. Имена сотрудника и его начальника в одной записи не должны совпадать. При добавлении и изменении записи в таблице emp_mgr это требование предъявляется к новой записи, которая до подтверждения транзакции располагается во временной таблице с именем inserted. Этому правилу соответствуют следующие SQL-операторы:

IF EXISTS (SELECT * FROM inserted WHERE mgr=emp)BEGIN ROLLBACK TRAN RAISERROR('САМ СЕБЕ НАЧАЛЬНИК',16,10) RETURNEND

Правило 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 RETURNEND

или (что эквивалентно)

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 RETURNEND

В соответствии с правилом 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) RETURNEND

Оператор 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 ENDEND

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

ALTER TABLE emp_mgr DROP CONSTRAINT fk_emp

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

<28 29 303132 33 34 >

Дата добавления: 2015-08-26; просмотров: 843;