Брешенков А.В., Галямова Е.В., Ефремов С.В. - Процедуры и функции в Oracle, Триггеры в Oracle (1075561), страница 4
Текст из файла (страница 4)
Для системных триггеровразрешены CREATE, ALTER, DROP TABLE и ALTER..COMPILE выражения.Процедура, вызываемая в триггере, не может обращаться к предыдущейтранзакции, потому что она выполняется в контексте выполнения триггера.Выражения внутри триггера могут обращаться к удаленным объектам схемы.Однако следует обратить в таком случае внимание на совпадение подписей, настроеквремени, чтобы не произошло сбоя.3.2.2 Ограничение на системные триггеры.Только утвержденные триггеры запускаются. Например, если вы создаете триггер,который должен содержать опцию CREATE, то при создании триггера он сам запускатьсяне будет, потому что информация о создании такого триггера еще не подтвердилась в базеданных.
Чтобы было яснее, рассмотрим следующий пример.CREATE OR REPLACE TRIGGER my_triggerAFTER CREATE ON DATABASEBEGINNULL;END;3.2.3 Привилегии, которые нужны для работы с триггерами.Для того чтобы создать триггер, Вам необходимо иметь системную привилегиюCREATE TRIGGER, а также одну из следующих привилегий:Владеть таблицей, определенной в заголовке триггера.Иметь ALTER привилегию над таблицей, определенной в заголовкетриггера.Иметь системную привилегию ALTER ANY TABLE.Системная привилегия CREATE TRIGGER включена в RESOURCE роль, котораяприсвоена пользователю HR.Чтобы создать триггер базы данных, Вы должны обладать ADMINISTERDATABASE TRIGGER привилегией.
Если эта привилегия позже будет снята с Вас, то высможете удалить созданный триггер, но не изменить его.4Управление триггерами.Триггеры – это еще один вид объектов, которыми Вы можете управлять с помощьюстраницы просмотра объектов (Object Browser). Вы также можете создавать и изменять ихс помощью страницы SQL команд или командной строки SQL.4.1Создание триггеров с помощью страницы SQL команд.Вы можете создать объекты данного типа с помощью страницы SQL команд, дляэтого вам необходимо выполнить следующие действия:1.Войдите на страницу базы данных с помощью пароля, полученного отпреподавателя.2.На главной странице щелкните мышкой по иконке «SQL», чтобы отобразитьстраницу управления SQL.3.На странице SQL нажмите на иконку «SQL Commands», чтобы перейти наследующую страницу.4.Для начала создайте таблицу emp_audit, к которой будет подключен триггерaudit_sal.
Для этого введите в появившемся окне PL/SQL код, показанный впримере №1.5.Выберите (выделите) код создания таблицы и нажмите кнопку «RUN». Послеэтого сотрите все строчки, чтобы повторно не создать таблицу.6.Введите код, показанный на рисунке №1.Рис.
4 Создание триггера audit_sal с помощью SQL кода.7.Нажмите кнопку «RUN».8.Если Вы хотите сохранить PL/SQL код для будущего использования, нажмитекнопку «SAVE».В поле «NAME» введите название Вашего кода. Вы можете также ввести9.дополнительные опции. Нажмите кнопку «SAVE», чтобы сохранить Ваш код.Для доступа к сохраненным триггерам нажмите на закладку «Saved SQL» и10.выберите необходимый Вам триггер.4.2Создание триггеров с помощью страницы просмотра объектов.Вы можете создать объекты данного типа с помощью страницы просмотраобъектов, для этого вам необходимо выполнить следующие действия:1.Войдите на страницу базы данных с помощью пароля, полученного отпреподавателя.2.На главной странице щелкните мышкой по треугольнику справа откнопки «Object Browser», чтобы появился выпадающий список.3.Выберите из списка пункт «Create» --} «Trigger».4.Введите имя таблицы «employees», к которой будет создан триггер, инажмите кнопку «Next».
Также вы можете не вводить имя таблицы, авыбрать его из списка.5.Введите имя триггера «emp_salary_trigger», проверьте, не стоит лигалочка напротив «Preserve Case»(не учитывать регистр).6.Выберите как точку запуска триггера «After».7.Выберите в опциях «update of».8.Выберите из списка колонок «Salary».9.Проверьте, стоит ли галочка напротив«For Each Row». Не вводитеничего в поле «WHEN».10.В поле тела триггера введите код из примера №1.Рис.
5 Создание триггера emp_salary_trigger с помощью Object Browser.11.Нажмите кнопку «NEXT».12.Нажмите кнопку «SQL», чтобы посмотреть, как выглядят все сделанныедействия в виде SQL кода.13.4.3Нажмите кнопку «FINISH».Просмотр триггеров с помощью страницы просмотра объектов.Для того, чтобы посмотреть, какие триггеры существуют в Вашей базе используетестраницу просмотра объектов «Object Browser».1Войдите на страницу базы данных с помощью пароля, полученного от преподавателя.2На главной странице щелкните мышкой по объекту «Object Browser», чтобыоткрылась страница просмотра объектов.3Из выпадающего списка выберите триггер, затем щелкните по имени триггера,который создали в предыдущем пункте («emp_salary_trigger»).4Нажмите кнопку «Object Details», чтобы посмотреть информацию о триггере.Рис.
6. Информация о триггере «emp_salary_trigger».Посмотрите добавочную информацию с помощью кнопок «Code», «Errors»,5.«SQL».4.4Создание триггеров с опциями «AFTER» и «FOR EACH ROW».В примере №1 рассматривается код создания триггера «audit_sal», связанного стаблицей «employees», он запускается после того, как были произведены изменения сколонкой «salary» и записывает данные в таблицу аудита.С помощью опции «FOR EAH ROW» триггер вносит новую информацию в таблицу«emp_audit» после каждого изменения зарплаты в таблице «emplyees». Запись содержитполя ID служащего, дату изменения, старую и новую зарплаты. Обратите внимания на то,как идет обращение к старой и новой колонке зарплаты.С помощью ключевого слова «AFTER» извлекать данные и изменять одну и ту жетаблицу. Однако он может делать это только после того, как изменения были приняты.Из-за того, что триггер использует выражение «FOR EACH ROW», он можетвызываться несколько раз подряд.
Так будет происходить после удаления или изменениянескольких строк. Если Вы только хотите зафиксировать факт внесения изменений, и Вамне нужна информация о них, вы можете не использовать эту опцию.Пример 1. Создание триггера с опцией «AFTER».-- create a table to use for with the trigger in this example if-- it has not already been created previously-- if the table does not exist, the trigger will be invalidCREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,new_sal NUMBER(8,2), old_sal NUMBER(8,2) );-- create or replace the triggerCREATE OR REPLACE TRIGGER audit_salAFTER UPDATE OF salary ON employees FOR EACH ROWBEGIN-- bind variables are used here for valuesINSERT INTO emp_audit VALUES( :OLD.employee_id, SYSDATE,:NEW.salary, :OLD.salary );END;/-- fire the trigger with an update of salaryUPDATE employees SET salary = salary * 1.01 WHERE manager_id = 122;-- check the audit table to see if trigger was firedSELECT * FROM emp_audit;4.5.Создание триггеров с опциями «BEFORE» и условием «WHEN».В примере №2 рассматривается триггер с опцией «BEFORE», который запускаетсяпосле изменения каждой строки.
Обратите внимание на использование условия «WHEN».Пример 2. Создание триггера с опцией «BEFORE».-- create a temporary tableCREATE TABLE emp_sal_log (emp_id NUMBER, log_date DATE,new_salary NUMBER, action VARCHAR2(50));CREATE OR REPLACE TRIGGER log_salary_increase -- create a triggerBEFORE UPDATE of salary ON employees FOR EACH ROWWHEN (OLD.salary < 8000)BEGININSERT INTO emp_sal_log (emp_id, log_date, new_salary, action)VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');END;/-- update the salary with the following UPDATE statement-- trigger fires for each row that is udpatedUPDATE employees SET salary = salary * 1.01 WHERE department_id = 60;-- view the log tableSELECT * FROM emp_sal_log;4.6.
Создание триггеров с опцией «INSTEAD OF».В примере №3 создается вид с несколькими починенными таблицами. Обратитевнимание на использование ключевого слова «JOIN».Пример №3. Создание вида, который изменяется триггером c «INSTEAD OF».CREATE OR REPLACE VIEW my_mgr_view ASSELECT ( d.department_id || ' ' || d.department_name) "Department",d.manager_id, e.first_name, e.last_name, e.email, e.hire_date "Hired On",e.phone_number, e.salary, e.commission_pct,(e.job_id || ' ' || j.job_title) "Job Class"FROM departments dJOIN employees e ON d.manager_id = e.employee_idJOIN jobs j ON e.job_id = j.job_idORDER BY d.department_id;В примере №4 рассматривается триггер, который будет переносить изменения ввиде в таблицы.Пример№4.
Создание триггера с опцией «INSTEAD OF».CREATE OR REPLACE TRIGGER update_my_mgr_viewINSTEAD OF UPDATE ON my_mgr_viewFOR EACH ROWBEGIN-- allow the following updates to the underlying employees tableUPDATE employees SETlast_name = :NEW.last_name,first_name = :NEW.first_name,email = :NEW.email,phone_number = :NEW.phone_number,salary = :NEW.salary,commission_pct = :NEW.commission_pctWHERE employee_id = :OLD.manager_id;END;/После того как был создан данный триггер, можно внести изменения в вид,которые перенесутся в таблицу.UPDATE my_mgr_view SET first_name = 'Denis' WHERE manager_id = 114;4.7.Создание триггера с перехватчиком ошибок.В примере №5 показан код, создающий триггер с перехватчиком ошибок.
Впримере возникает исключение, если какой-либо процесс попытается поменять IDменеджера служащего.Пример №5. Триггер с перехватчиком ошибок.-- create a temporary tableCREATE TABLE emp_except_log (emp_id NUMBER, mgr_id_new NUMBER,mgr_id_old NUMBER, log_date DATE, action VARCHAR2(50));CREATE OR REPLACE TRIGGER emp_log_update -- create a triggerBEFORE UPDATE ON employees FOR EACH ROWDECLAREmgrid_exception EXCEPTION;BEGINIF (:NEW.manager_id <> :OLD.manager_id) THENRAISE mgrid_exception;END IF;INSERT INTO emp_except_log (emp_id, mgr_id_new, mgr_id_old, log_date, action)VALUES (:NEW.employee_id, :NEW.manager_id, :OLD.manager_id,SYSDATE, 'Employee updated');EXCEPTIONWHEN mgrid_exception THENINSERT INTO emp_except_log (emp_id, mgr_id_new, mgr_id_old, log_date, action)VALUES (:NEW.employee_id, :NEW.manager_id, :OLD.manager_id,SYSDATE, 'Employee manager ID updated!');END;/-- update employees with the following UPDATE statements, firing triggerUPDATE employees SET salary = salary * 1.01 WHERE employee_id = 105;-- the trigger raises an exception with this UPDATEUPDATE employees SET manager_id = 102 WHERE employee_id = 105;-- view the log tableSELECT * FROM emp_except_log;4.8.Создание триггера, запускающегося один раз при каждом изменении.В примере №6 рассматривается триггер, запускающийся один раз при каждомизменении.
Для этого выражение «FOR EACH ROW» было опущено. Для того, чтобытриггер срабатывал при нескольких условиях, были использованы ключевые слова«IF..THEN» и производные от ключевых слов, такие как «INSERTING».Пример №6. Триггер, запускающийся один раз при каждом изменении.-- create a log tableCREATE TABLE emp_update_log (log_date DATE, action VARCHAR2(50));-- create a triggerCREATE OR REPLACE TRIGGER log_emp_updateAFTER UPDATE OR INSERT ON employeesDECLAREv_action VARCHAR2(50);BEGINIF UPDATING THENv_action := 'A row has been updated in the employees table';END IF;IF INSERTING THENv_action := 'A row has been inserted in the employees table';END IF;INSERT INTO emp_update_log (log_date, action)VALUES (SYSDATE, v_action);END;/-- fire the trigger with an updateUPDATE employees SET salary = salary * 1.01 WHERE department_id = 60;INSERT INTO employees VALUES(14, 'Belden', 'Enrique', 'EBELDEN','555.111.2222','31-AUG-05', 'AC_MGR', 9000, .1, 101, 110);-- view the log tableSELECT * FROM emp_update_log;-- clean up: remove the inserted recordDELETE FROM employees WHERE employee_id = 14;4.9.Создание «LOGON» и «LOGOFF» триггера.Вы можете создавать триггеры, связанные с подключением или отключениемопределенного пользователя от базы данных.В примере №7 рассматривается триггер, добавляющий запись в таблицу логов,когда пользователь подключается к схеме HR.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
