Информационное обеспечение
REFERENCING NEW AS NEW1 OLD AS OLD1
жүктеу/скачать 256,49 Kb.
|
REFERENCING NEW AS NEW1 OLD AS OLD1FOR EACH ROW BEGIN :new1.department_id:=100; END; / Триггеры принимают участие в транзакциях. Если в триггере инициирует- ся исключение, то будет выполнен откат соответствующей транзакции. Если триггер сам изменяет данные, то все изменения становятся частью общей тран- закции. В триггере нельзя применять операторы управления транзакциями COMMIT, ROOLBACK и SAVEPOINT. Общий синтаксис создания триггеров: CREATE [OR REPLACE] TRIGGER имя_триггера {BEFOR |AFTER | INSERT | DELETE | UPDATE | UPDATE OF COLUMN LIST} ON имя_таблицы [FOR EACH ROW] [WHEN () ] [DECLARE ...] BEGIN ... исполняемые_операторы [EXCEPTION ... обработка_исключений] END [имя_триггера]; Триггер, заданный для операции UPDATE, может быть определен для всей таблицы или только для определенных полей –UPDATE OF COLUMN LIST. Определение WHEN позволяет задать дополнительную логику для исклю- чения ненужных запусков триггера. Предложение WHEN может использовать- ся только в триггерах уровня записи. Раздел DECLARE не обязательный, если в триггере не объявляются ло- кальные переменные. Триггер, использующий последовательность в качестве данных для встав- ки в ключевое поле таблицы: CREATE OR REPLACE TRIGGER EMPLOYEES_INS BEFORE INSERT ON employees FOR EACH ROW BEGIN /* присваиваем код */ SELECT employees_seq.NEXTVAL INTO :new.employee_id FROM dual; END employees_INS; / Триггер EMPLOYEES_INS запускается на событие INSERT для каждой строки. В нем автоматически присваивается значение полю employee_id на ос- новании созданной ранее последовательности employees_seq. Триггер, изменяющий формат данных при вводе в таблицу: CREATE OR REPLACE TRIGGER EMPLOYEES_UPD BEFORE INSERT OR UPDATE ON employees FOR EACH ROW BEGIN -- приводим поля first_name и last_name к нужному формату :new.first_name:= UPPER(substr(:new.first_name,1,1))||LOWER(substr(:new.first_name,2)); :new.last_name:= UPPER(substr(:new.last_name,1,1))||LOWER(substr(:new.last_name,2)); :new.email:=UPPER(:new.email); END EMPLOYEES_UPD; / Триггер EMPLOYEES_UPD обрабатывает два события INSERT и UPDATE и приводит к нужному формату поля с фамилией и адресом. Рассмотрим триггер, который выполняет функцию аудита. Для регистра- ции всех выполненных операций необходимо создать таблицу useraudit: CREATE TABLE useraudit ( table_name VARCHAR2(30), oper_name CHAR(1), pk_key VARCHAR2(200), column_name VARCHAR2(30), old_value VARCHAR2(200), new_value VARCHAR2(200), username VARCHAR2(20), dateoper DATE); Также создадим процедуру, которая будет сохранять данные: CREATE OR REPLACE PROCEDURE setaudit ( vtable_name IN VARCHAR2, voper_name IN CHAR, vpk_key IN VARCHAR2, vcolumn_name IN VARCHAR2, vold_value IN VARCHAR2, vnew_value IN VARCHAR2 ) IS -- PRAGMA AUTONOMOUS_TRANSACTION; BEGIN IF vold_value<>vnew_value OR vOper_name IN ('I','D') THEN INSERT INTO useraudit (table_name, oper_name, pk_key, column_name, old_value, new_value, username, dateoper) VALUES (vtable_name, voper_name, vpk_key, vcolumn_name, vold_value, vnew_value, USER, SYSDATE); -- COMMIT; END IF; END; / В этой процедуре COMMIT закомментирован, так как планируется вызы- вать ее из триггеров, где использование COMMIT запрещено. Однако, если в процедуре добавить указание на использование автономной транзакции: PRAGMA AUTONOMOUS_TRANSACTION, то в этом случае наличие опера- тора завершения транзакции обязательно. Если процедуру setaudit объявить, как работающую в автономной транзакции, то она будет регистрировать не только ФАКТ изменений в БД, но и даже ПОПЫТКУ изменения данных, даже если основная транзакция не будет зафиксирована и завершится откатом. Триггер, который будет запускаться на события изменения данных, имеет следующий синтаксис: CREATE OR REPLACE TRIGGER employees_aud AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW DECLARE op CHAR (1) := 'I'; BEGIN CASE WHEN INSERTING THEN op:= 'I'; setaudit ('EMPLOYEES', op, :NEW.employee_id, 'SALARY', NULL, :NEW.salary); setaudit ('EMPLOYEES', op, :NEW.employee_id, 'JOB_ID', NULL, :NEW.job_id); setaudit ('EMPLOYEES', op, :NEW.employee_id, 'DEPARTMENT_ID', NULL, :NEW.department_id); WHEN UPDATING('SALARY') OR UPDATING('JOB_ID') OR UPDATING('DEPARTMENT_ID') THEN op:='U'; setaudit ('EMPLOYEES', op, :NEW.employee_id, 'SALARY', :OLD.salary, :NEW.salary); setaudit ('EMPLOYEES', op, :NEW.employee_id, 'JOB_ID', :OLD.job_id, :NEW.job_id); setaudit ('EMPLOYEES', op, :NEW.employee_id, 'DEPARTMENT_ID', :OLD.department_id, :NEW.department_id); WHEN DELETING THEN op:='D'; setaudit ('EMPLOYEES', op, :old.employee_id, 'SALARY', :OLD.salary, NULL); setaudit ('EMPLOYEES', op, :old.employee_id, 'JOB_ID', :OLD.job_id, NULL); setaudit ('EMPLOYEES', op, :old.employee_id, 'DEPARTMENT_ID', :OLD.department_id, NULL); ELSE null; END CASE; END employees_aud; / Как видно из примера, в Oracle имеется набор функций – предикатов триг- гера, который позволяет определить, какая операция вызвала триггер. Это пре- дикаты INSERTING, UPDATING, DELETING. Функции возвращают TRUE, ес- ли триггер был запущен в ответ на соответствующую операцию. Функция UP- DATING имеет перегруженную версию, принимающую в качестве аргумента имя конкретного столбца. Для проверки работы триггера необходимо вставить запись с новым сотрудником, а затем просмотреть таблицу аудита: INSERT INTO employees (first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) VALUES ('Иван', 'Иванов', 'ivan', '222.7777', to_date('01.11.2004','dd.mm.yyyy'), 'IT_PROG', 6500, 0, 103, 60); SELECT * FROM useraudit; жүктеу/скачать 256,49 Kb. Достарыңызбен бөлісу:
|