Пример 6. Триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных
В примере происходит отмена всех изменений при невозможности реализовать хотя бы одно из них. Создадим триггер, позволяющий отменять изменение только некоторых записей и выполнять изменение остальных.
В этом случае триггер выполняется не после изменения записей, а вместо команды изменения.
ALTER TRIGGER Триггер_upd ON Сделка INSTEAD OF UPDATE AS DECLARE @k INT, @k_old INT DECLARE @x INT, @x_old INT, @y INT DECLARE @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM inserted DECLARE CUR2 CURSOR FOR SELECT КодСделки, КодТовара,Количество FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=остаток FROM Склад WHERE КодТовара=@x IF @o>=-@y BEGIN RAISERROR('изменение',16,10) UPDATE Сделка SET количество=@y, КодТовара=@x WHERE КодСделки=@k
UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old
IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара, Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x END ELSE RAISERROR('запись не изменена',16,10) FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2
ТРИГГЕРЫ В РЕКУРСИВНЫХ СТРУКТУРАХ |
|
Введение в рекурсивные структуры Рассмотрим создание таблицы, реализующей рекурсивную иерархию, на примере данных, описывающих отношения подчиненности между сотрудниками. В таблице 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, в котором для каждого сотрудника определено количество его подчиненных. Для удобства иллюстрации в качестве имени сотрудника и его начальника будут использоваться латинские буквы. Например, ввод данных в таблицу осуществляется операторами: 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 обеспечивается ограничением первичного ключа и не требует дополнительных 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 Ниже приведен текст триггеров, поддерживающих целостность данных в иерархических структурах. Предполагается, что триггеры обрабатывают ввод, изменение или удаление одной записи. |
|
Добавление записи в рекурсивную структуру и изменение записи в рекурсивной структуре Пример 1. Триггер для добавления записи в таблицу. Попытка подчинить сотрудника с именем ‘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 транзитивное замыкание Пример .2. Триггер для изменения записи в таблице. Выполнение команды 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 Удаление записи из рекурсивной структуры Пример 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 |