Общая характеристика реляционной модели данных

 существует  соответствующее   значение  в   родительском  ключе.       

 Ограничение MUTATING_TABLE, включенное в этот пример,  позволяет        

 использовать   этот   триггер    в   сочетании   с    триггерами       

 UPDATE_SET_DEFAULT  и  UPDATE_CASCADE.   Это  исключение   можно       

 удалить, если данный  триггер будет использоваться  в одиночку. 

 

 

        CREATE TRIGGER emp_dept_check       

BEFORE INSERT OR UPDATE OF deptno ON emp     

   FOR EACH ROW WHEN (new.deptno IS NOT NULL) 

 

 

        /* Перед появлением в таблице EMP нового значения DEPTNO,          

 возбудить этот триггер,  чтобы проверить, что это значение           

 внешнего ключа (DEPTNO) существует в таблице DEPT. */ 

 

 

        DECLARE         

dummy INTEGER;  /* используется для извлечения из курсора */         

 invalid_department EXCEPTION;         

valid_department EXCEPTION;         

mutating_table EXCEPTION;         

PRAGMA EXCEPTION_INIT (mutating_table, -4091); 

 

 

        /* Курсор, используемый для проверки, что значение родительского           

 ключа существует. Если  оно существует, блокировать строку           

 родительской таблицы,  чтобы другая транзакция не  смогла          

 удалить или изменить  родительский ключ до подтверждения  или          

 отката нашей транзакции. */ 

 

 

        CURSOR dummy_cursor (dn NUMBER) IS         

SELECT deptno         

FROM dept         

WHERE deptno = dn         

FOR UPDATE OF deptno; 

 

 

        BEGIN         

OPEN dummy_cursor (:new.deptno);         

FETCH dummy_cursor INTO dummy; 

 

 

        /* Проверить родительский ключ. Если он не найден, инициировать          

 определенную пользователем  ошибку. Если он найден, закрыть          

 курсор, прежде чем  продолжится предложение, возбудившее           

 этот триггер. */ 

 

 

        IF dummy_cursor%NOTFOUND THEN         

RAISE invalid_department;       

ELSE         

RAISE valid_department;       

END IF; 

 

 

        EXCEPTION         

WHEN invalid_department THEN   

         raise_application_error(-20000,             

'Invalid Department Number ' || TO_CHAR(:new.deptno));          

WHEN valid_department THEN           

CLOSE dummy_cursor;         

WHEN mutating_table THEN           

NULL;       

 END; 

 

 

        ТРИГГЕР  UPDATE  И  DELETE  RESTRICT  ДЛЯ  РОДИТЕЛЬСКОЙ ТАБЛИЦЫ.       

 Следующий   триггер   определяется   по   таблице   DEPT,  чтобы       

 реализовать  ссылочное  действие  UPDATE  и  DELETE  RESTRICT по       

 первичному ключу таблицы DEPT: 

 

 

        CREATE TRIGGER dept_restrict       

AFTER DELETE OR UPDATE OF deptno ON dept       

 FOR EACH ROW 

 

 

        /* Перед удалением или изменением  в таблице DEPT значения          

 первичного ключа (DEPTNO) проверить, что в таблице EMP           

 нет зависимых значений  внешнего ключа; если они есть,           

 то возвратить ошибку. */ 

 

 

        DECLARE         

dummy INTEGER;  /* используется для извлечения из курсора */         

 employees_present EXCEPTION;         

employees_not_present EXCEPTION; 

 

 

        /* Курсор, используемый для проверки зависимых внешних ключей */ 

 

 

        CURSOR dummy_cursor (dn NUMBER) IS         

SELECT deptno FROM emp WHERE deptno = dn; 

 

 

        BEGIN         

OPEN dummy_cursor (:old.deptno);         

FETCH dummy_cursor INTO dummy; 

 

 

        /* Если зависимый внешний ключ найден, инициировать          

 определенную пользователем  ошибку. Если он не найден,          

 закрыть курсор, прежде  чем продолжится предложение,          

 возбудившее этот триггер. */ 

 

 

        IF dummy_cursor%FOUND THEN         

RAISE employees_present;  /* существуют зависимые строки */       

ELSE         

RAISE employees_not_present;  /* нет зависимых строк */       

END IF; 

 

 

        EXCEPTION         

WHEN employees_present THEN      

      raise_application_error(-20001,             

'Employees Present in Department '||TO_CHAR(:new.deptno));            

CLOSE dummy_cursor;         

WHEN employees_not_present THEN           

CLOSE dummy_cursor;       

END; 

 

 

        Замечания:  Этот  триггер  не  будет  работать  с самоссылочными       

 таблицами (т.е. таблицами,  содержащими как  первичный/уникальный        

 ключ, так и внешний  ключ). 

 

 

        Кроме того,  этот триггер  не позволяет  триггерам зацикливаться       

(например, A возбуждает B, который  возбуждает A). 

 

 

        ТРИГГЕРЫ  UPDATE  И  DELETE  SET  NULL ДЛЯ РОДИТЕЛЬСКОЙ ТАБЛИЦЫ.       

 Следующий  триггер,  определяемый  по  таблице  DEPT,  реализует       

 ссылочное действие UPDATE и DELETE SET NULL по первичному  ключу    

    таблицы DEPT: 

 

 

        CREATE TRIGGER dept_set_null       

AFTER DELETE OR UPDATE OF deptno ON dept       

 FOR EACH ROW 

 

 

        /* Перед удалением или изменением  в таблице DEPT значения          

 первичного ключа (DEPTNO) сбросить в NULL все зависимые          

 значения внешнего  ключа в таблице EMP. */ 

 

 

        BEGIN         

IF UPDATING AND :OLD.deptno != :NEW.deptno THEN           

UPDATE emp SET emp.deptno = NULL             

WHERE emp.depnto = :old.deptno;          

END IF;       

 END; 

 

 

        

 ТРИГГЕР  DELETE  CASCADE  ДЛЯ  РОДИТЕЛЬСКОЙ  ТАБЛИЦЫ.  Следующий       

 триггер,  определяемый  по  таблице  DEPT,  реализует  ссылочное       

 действие DELETE CASCADE по первичному  ключу таблицы DEPT: 

 

 

        CREATE TRIGGER dept_del_cascade       

AFTER DELETE ON dept       

 FOR EACH ROW 

 

 

        /* Перед удалением строки из  таблицы DEPT удалить из таблицы           

EMP все строки, имеющие  такое же значение DEPTNO. */ 

 

 

        BEGIN        

  DELETE FROM emp           

WHERE emp.depnto = :old.deptno;       

 END; 

 

 

        Замечание: Обычно  код для  DELETE CASCADE  объединяют вместе  с       

 кодом для UPDATE SET NULL  или UPDATE SET DEFAULT, чтобы  учесть       

 как обновления, так  и удаления в одном триггере.  

 

 

        ТРИГГЕР  UPDATE  CASCADE  ДЛЯ  РОДИТЕЛЬСКОЙ  ТАБЛИЦЫ.  Следующий       

 триггер гарантирует,  что при  изменении номера отдела в  таблице        

DEPT это изменение будет  распространено на все зависимые  внешние       

 ключи в таблице  EMP: 

 

 

        /* Создать последовательность, номер  которой будет применяться          

 как флажок, указывающий,  что данный столбец подвергается           

 обновлению. */ 

 

 

        create sequence update_sequence increment by 1 maxvalue 5000       

cycle; 

 

 

        CREATE PACKAGE integritypackage AS         

updateseq NUMBER;       

END integritypackage; 

 

 

        CREATE OR REPLACE PACKAGE BODY integritypackage AS       

END integritypackage; 

 

 

        ALTER TABLE emp ADD update_id NUMBER;  /* добавить флажок */ 

 

 

        CREATE TRIGGER dept_cascade1       

BEFORE UPDATE OF deptno ON dept       

DECLARE         

 dummy NUMBER; 

 

 

        /* Перед обновлением таблицы  DEPT (это триггер предложения),          

 сгенерировать новый  номер последовательности и назначить           

 его общей переменной UPDATESEQ из пакета INTEGRITYPACKAGE. */ 

 

 

        BEGIN         

SELECT update_sequence.NEXTVAL           

INTO dummy           

FROM dual;         

integritypackage.updateseq := dummy;       

END; 

 

 

        CREATE TRIGGER dept_cascade2       

AFTER DELETE OR UPDATE OF deptno ON dept       

 FOR EACH ROW 

 

 

        /* Для каждого обновляемого  значения DEPTNO в таблице DEPT          

 выполнить каскадное  обновление зависимых внешних  ключей          

 в таблице EMP. Выполняя  каскадное обновление, проверять,          

 чтобы порожденная  строка уже не была обновлена  этим же          

 триггером. */ 

 

 

        BEGIN         

IF UPDATING THEN           

UPDATE emp        

      SET deptno := :new.deptno,             

update_id = integritypackage.updateseq -- из 1-го триггера             

WHERE emp.deptno = :old.deptno             

AND update_id IS NULL; 

 

 

              /* этот флажок пуст только в случае, если он не был 

                обновлен 3-м триггером для того же самого предложения                

 триггера */ 

 

 

          END IF; 

 

 

          IF DELETING THEN 

 

 

          /* Перед удалением строки из таблицы DEPT, удалить из таблицы            

EMP все строки, имеющие  такое же значение DEPTNO, что и            

 в удаляемой строке  таблицы DEPT */ 

 

 

            DELETE FROM emp             

WHERE emp.deptno = :old.deptno;         

END IF;       

END; 

 

 

        CREATE TRIGGER dept_cascade3       

AFTER UPDATE OF deptno ON dept       

BEGIN         

UPDATE emp           

SET update_id = NULL           

WHERE update_id = integritypackage.updateseq;       

 END; 

 

 

        Замечание: Так как этот  триггер обновляет таблицу EMP,  триггер       

EMP_DEPT_CHECK, если он включен,  также возбуждается.   Возникает       

 ошибка  (поскольку  таблица  EMP  мутирующая),  которую  триггер       

EMP_DEPT_CHECK перехватывает.  Вы должны тщательно отладить  все       

 триггеры,   для   которых   требуется   перехват   ошибок, чтобы       

 убедиться, что они  всегда работают правильно в  вашем окружении. 

 

 

 

Реализация комплексных  ограничений контроля 

 

 

        Триггеры могут  реализовывать правила  целостности, отличные  от       

 ссылочной  целостности.   Например,  следующий триггер выполняет        

 сложную проверку, прежде  чем разрешает выполниться  предложению        

 триггера.  Комментарии  внутри кода  объясняют, что  делает этот       

 триггер. 

 

 

        CREATE TRIGGER salary_check       

BEFORE INSERT OR UPDATE OF sal, job_classification ON emp       

FOR EACH ROW       

DECLARE         

minsal                NUMBER;         

maxsal                NUMBER;         

salary_out_of_range   EXCEPTION; 

 

 

        BEGIN 

 

 

        /* Извлечь в MINSAL и MAXSAL минимальное  и максимальное          

 жалованья из справочной  таблицы SALGRADE по заданной          

 новой должности сотрудника. */ 

 

 

          SELECT minsal, maxsal INTO minsal, maxsal FROM salgrade           

WHERE job_classification = :new.job_classification;  

 

 

          /* Если новое жалованье сотрудника выходит за допустимый            

 диапазон, возбуждается  исключение, возвращается сообщение             

 об ошибке, и висящее  предложение INSERT или UPDATE,            

 которое возбудило  триггер, откатывается. */ 

 

 

          IF (:new.sal < minsal OR :new.sal > maxsal) THEN           

 RAISE salary_out_of_range;         

END IF; 

 

 

        EXCEPTION         

WHEN salary_out_of_range THEN           

raise_application_error (-20300,             

'Salary ' || TO_CHAR(:new.sal) ||             

' out of range for job classification ' ||             

:new.job_classification ||             

' for employee ' || :new.name);         

WHEN NO_DATA_FOUND THEN       

     raise_application_error (-20322,             

'Invalid Job Classification ' || :new.job_classification);        

 END; 

 

 

 

Триггеры и комплексные  проверки полномочий

------------------------------------------ 

 

 

        Триггеры  часто  используются  для  реализации  сложных проверок       

 защиты для данных  таблицы.   Применяйте триггеры лишь для  таких       

 проверок  полномочий,   которые  нельзя   выполнить  с   помощью