Информационное обеспечение
жүктеу/скачать 256,49 Kb.
|
| Ошибки при изменении таблицы. Довольно часто при разработке триггеров на изменение данных в таблице приходится сталкиваться с проблемой «мута- ции» триггеров, а именно с ошибкой Oracle:
ORA-04091: table … is mutating. trigger/function may not see it. Эта ошибка возникнет, например, при разработке триггера на обновление таблицы сотрудников, при помощи которой проводится проверка величины зара- ботной платы, чтобы она не превышала среднюю по отделу более чем на 20 %. Таким образом, мутирующая таблица – это либо таблица, обновляемая в данный момент командами UPDATE, DELETE, INSERT, либо таблица, обнов- ление которой может потребоваться вследствие срабатывания каскадного уда- ления в подчиненной таблице (ограничение DELETE CASCADE). Для тригге- ров, принадлежащих изменяемой таблице, эта таблица является мутирующей (к операторным триггерам это понятие не применяется). Кроме того, мутирую- щей таблицей является любая другая, ссылающаяся на таблицу триггера через ограничение внешнего ключа, что позволяет СУБД запретить чтение несогла- сованного набора данных. CREATE OR REPLACE TRIGGER employees_sal BEFORE UPDATE OF salary ON employees FOR EACH ROW DECLARE avr_sal NUMBER; -- PRAGMA AUTONOMOUS_TRANSACTION; BEGIN SELECT avg(salary) INTO avr_sal FROM employees WHERE department_id = :new.department_id; --COMMIT; IF avr_sal < :new.salary*1.2 THEN RAISE_APPLICATION_ERROR (-20001, 'заработная плата превышает среднюю по отделу на 20 %'); END IF; END; / При попытке обновить заработную плату, например: UPDATE employees SET salary=9000 WHERE employee_id=109; будет получено сообщение об ошибке, однако есть несколько простых приемов, которые могут решить данную проблему, а именно: Если триггер выполняется как автономная транзакция, т. е. объявлен с инструкцией PRAGMA AUTONOMOUS_TRANSACTION и в нем выполняется инструкция COMMIT, тогда в нем можно запрашивать содержимое таблицы, но не изменять его. Таким образом, убрав комментарии в последнем примере, можно достаточно просто решить проблему мутации. Триггер уровня строки не может считывать или записывать данные из таблицы, с которой он связан, но данное ограничение не касается триггеров уровня инструкции, что дает возможность провести необходимые действия. В более сложных ситуациях, когда невозможно решить проблему указан- ными способами, используют или комбинацию переменных пакета с двумя триггерами, или составные COMPOUND-триггеры. В первом случае необходи- мо на уровне пакета объявить глобальную переменную типа ассоциативного массива, в которой можно будет сохранить необходимые данные из мутирую- щей таблицы с помощью операторного триггера, а затем использовать данные, записанные в пакетной переменной с помощью строчного триггера. Второй способ предполагает знание синтаксиса COMPOUND-триггера. Появившиеся в версии 11g, эти триггеры имеют в одном блоке обработку всех видов DML-триггеров, могут содержать переменные, которые живут на всем протяжении выполнения оператора, вызвавшего срабатывание триггера, и включают следующие секции: BEFORE STATEMENT; AFTER STATEMENT; BEFORE EACH ROW; AFTER EACH ROW. Некоторые правила написания: нет отдельной секции инициализации, но для этих целей можно исполь- зовать секцию BEFORE STATEMENT; нельзя обращаться к псевдозаписям OLD, NEW в секциях уровня опера- тора (BEFORE STATEMENT и AFTER STATEMENT); изменять значения полей псевдозаписи NEW можно только в секции BEFORE EACH ROW; исключения, сгенерированные в одной секции, нельзя обрабатывать в другой секции; если используется оператор GOTO, он должен указывать на код в той же секции. Составной триггер можно использовать для написания кода, соответству- ющего разным моментам времени события и совместного использования пере- менных, объявленных в секции DECLARE; для отбора строк, предназначенных для загрузки в другую таблиц, – в секции BEFORE EACH ROW или AFTER EACH ROW, которые затем вставляются в секцию AFTER STATEMENT; для более простого решения проблемы мутирующей таблицы. Общий синтаксис COMPOUND-триггера: CREATE OR REPLACE TRIGGER имя_триггера FOR {INSERT|UPDATE|DELETE}[OF имя_столбца, …] ON имя_таблицы COMPOUND TRIGGER [секция_объявления_переменных_триггера] BEFORE STATEMENT секция_операторного_триггера BEFORE EACH ROW секция_строчного_триггера AFTER EACH ROW секция_строчного_триггера AFTER STATEMENT секция_операторного_триггера END; / Рассмотрим пример COMPOUND-триггера: CREATE OR REPLACE TRIGGER sbiu_employees FOR INSERT OR UPDATE OF salary, job_id ON employees COMPOUND TRIGGER TYPE sal_rec_type IS RECORD (min_sal employees.salary%TYPE, max_sal employees.salary%TYPE); TYPE sal_tab_type IS TABLE of sal_rec_type INDEX BY varchar2(30); sal_tab sal_tab_type; BEFORE STATEMENT IS BEGIN sal_tab.delete; FOR rec IN (SELECT job_id, min(salary) min_sal, max(salary) max_sal FROM employees GROUP BY job_id) LOOP sal_tab(rec.job_id).min_sal:= rec.min_sal; sal_tab(rec.job_id).max_sal:= rec.max_sal; END LOOP; END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN IF :NEW.salary NOT BETWEEN sal_tab(:new.job_id).min_sal AND sal_tab(:new.job_id).max_sal THEN RAISE_APPLICATION_ERROR (-20505, 'out of range'); END IF; END BEFORE EACH ROW; END; / Триггеры DDL. Триггеры DDL запускаются на события, изменяющие объ- екты базы данных: таблицы, индексы, триггеры. Синтаксис триггеров следую- щий: CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE | AFTER} {событие_DDL} ON {DATABASE | SCHEMA} DECLARE объявление_переменных BEGIN ... код триггера END; Список событий, на которые реагируют DDL-триггеры, представлен в табл. 2. Таблица 2 Список событий DDL-триггеров
Применение триггеров позволяет отслеживать, фиксировать, проверять, запрещать действия с объектами всей базы данных или схемы пользователя. Например: CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON SCHEMA BEGIN RAISE_APPLICATION_ERROR (num => -20000, msg => 'Cannot drop object'); END; / Триггеры событий базы данных. Триггеры могут отслеживать и обрабаты- вать события базы данных. Триггер имеет следующий синтаксис: CREATE OR REPLACE TRIGGER имя_триггера {BEFORE | AFTER} {SERVERERROR | LOGON | LOGOFF | STARTUP | SHUTDOWN | SUSPEND }ON { DATABASE |SCHEMA } DECLARE Объявление_переменных BEGIN . . . код_триггера END; / Список событий, на которые реагируют триггеры уровня БД, приведен в табл. 3. Таблица 3 Список событий триггера уровня БД
Необходимо отметить, что системные триггеры уровня БД (ON DATABASE) запускаются только в SQL*Plus из-под учетной записи админи- стратора (as sysdba). Создадим таблицу и затем системный триггер: CREATE TABLE SYSTEM.AUDITBASE ( nzap NUMBER, polz VARCHAR2(20), tmin TIMESTAMP, oper VARCHAR2(50) ); CREATE OR REPLACE TRIGGER FIXUSERIN AFTER LOGON ON SCHEMA BEGIN INSERT INTO SYSTEM.AUDITBASE (NZAP, POLZ, TMIN, OPER) VALUES (1, USER, SYSDATE, 'User Is Log (off)'); END FIXUSERIN; / Замещающие триггеры INSTEAD OF создаются для необновляемых пред- ставлений (view) и служат для замещения DML-операций своим функциона- лом; позволяют производить операции вставки/обновления или удаления для необновляемых представлений. Это всегда триггер уровня записи (row level), который имеет доступ к псевдозаписям OLD и NEW, но не может изменять их. Например, создадим необновляемое представление: CREATE OR REPLACE VIEW my_mgr_view AS SELECT ( 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 d JOIN employees e ON d.manager_id = e.employee_id JOIN jobs j ON e.job_id = j.job_id ORDER BY d.department_id; Для того чтобы иметь возможность обновить его, создадим триггер INSTEAD OF: CREATE OR REPLACE TRIGGER update_my_mgr_view INSTEAD OF UPDATE ON my_mgr_view FOR EACH ROW BEGIN UPDATE employees SET last_name = :NEW.last_name, first_name = :NEW.first_name, email = :NEW.email, phone_number = :NEW.phone_number, salary = :NEW.salary, commission_pct = :NEW.commission_pct WHERE employee_id = :OLD.manager_id; END; / Порядок активизации триггеров. Если у таблицы имеется несколько типов триггеров, то они активизируются по следующей схеме: выполняется операторный триггер BEFORE (если их несколько, то ни- чего о порядке их выполнения сказать нельзя); выполняется строковый триггер BEFORE; выполняется активизирующий триггер DML-оператор с последующей проверкой всех ограничений целостности данных; выполняется строковый триггер AFTER с последующей проверкой всех ограничений целостности данных; выполняется операторный триггер AFTER. Включение/выключение триггеров. Хранящийся в базе данных триггер можно временно отключить, не удаляя его из базы данных. Для этого использу- ется следующая команда: ALTER TRIGGER имя_триггера DISABLE; Включить триггер через некоторый промежуток времени можно, исполь- зуя команду ALTER TRIGGER имя_триггера ENABLE; Запретить или разрешить запуск всех триггеров, связанных с некоторой таблицей, можно с помощью команды ALTER TABLE имя_таблицы {DISABLE | ENABLE} ALL TRIGGERS; Удаление триггеров. Удаление триггера из базы данных осуществляется с помощью команды DROP TRIGGER имя_триггера; Информацию о триггерах можно получить из представления словаря дан- ных USER_TRIGGERS. Планировщик задач. Хорошей альтернативой триггерам, позволяющей автоматизировать задания внутри БД Oracle, является Планировщик (Scheduler) – специальная программа, которая запускается в определенное время. В СУБД Oracle он реализован с помощью пакета DBMS_SCHEDULER. Пакет DBMS_SCHEDULER позволяет запускать хранимую процедуру или аноним- ный блок PL/SQL в моменты времени, вычисляемые по указанной пользовате- лем формуле. Он использует следующие основные понятия: Schedule (расписание); Program (программа); Job (плановое задание). Планировщик можно настроить на однократный запуск (непосредственно после запуска или в определенное время) и на многократный запуск (запуск по расписанию). Прежде всего для написания планировщика нужно, как правило, дать права на его создание командой: GRANT CREATE JOB TO имя_пользователя; Рассмотрим пример планировщика для однократного запуска анонимного блока: BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'simple_job', job_type => 'PLSQL_BLOCK', job_action => 'UPDATE employees SET salary=salary+1;', enabled => TRUE); END; / Здесь параметры: job_name – имя планировщика; job_type – тип запускаемо- го задания, который может принимать следующие значения: 'STORED_PROCEDURE' – хранимая процедура, 'PLSQL_BLOCK' – анонимный блок; job_action – то, что запускается; enabled – определяет включен или выклю- чен планировщик. В рассмотренном примере заработная плата каждому сотруднику увели- чится на один и планировщик удалится автоматически, что равносильно вы- полнению обычной команды UPDATE. Для запуска планировщика однократно в определенное время использу- ется следующий параметр пакета: start_date => SYSTIMESTAMP + INTERVAL '10' SECOND, где start_date – это время запуска, которое в данном примере составляет 10 с от текущего времени. Для многократного запуска введен параметр repeat_interval, пример ис- пользования которого представлен далее: repeat_interval => 'FREQ = MONTHLY; BYDAY = SUN, -1 SAT', В данном примере задание будет исполняться ежемесячно по воскресени- ям и последним субботам месяца. То есть в планировщике есть возможность указывать частоту, интервал и спецификатор запуска задания, например: FREQ = HOURLY; INTERVAL = 4 – запуск каждые 4 часа; FREQ = HOURLY; INTERVAL = 4; BYMINUTE = 10; BYSECOND = 30 – запуск каждые 4 часа на 10-й минуте, 30-й секунде; FREQ = YEARLY; BYYEARDAY = 276 – запуск каждый 276-й день года; FREQ = YEARLY; BYMONTH = MAR; BYMONTHDAY = 31 – запуск каждое 31-е марта. Частота – это обязательный компонент календарного выражения, кото- рый идентифицируется ключевым словом FREQ. Возможные значения – YEARLY, MONTHLY, DAILY, HOURLY, MINUTELY и SECONDLY. Интервал повторения идентифицируется ключевым словом INTERVAL и указывает, насколько часто база данных должна повторять задание. Спецификаторы предоставляют детальную информацию о том, когда должно запускаться задание. Возможные значения – BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, BYMINUTE и BYSEC- OND. Спецификаторы являются необязательными. В анонимном блоке можно использовать команды: --отключить планировщик DBMS_SCHEDULER.DISABLE('simple_job'); --остановить выполняющиеся задания: DBMS_SCHEDULER.STOP_JOB('simple_job'); --запустить задания DBMS_SCHEDULER.RUN_JOB('simple_job'); --запустить задания в фоновом режиме DBMS_SCHEDULER.RUN_JOB('simple_job', use_current_session => false); --удалить задания DBMS_SCHEDULER.DROP_JOB('simple_job'); или DBMS_SCHEDULER.DROP_JOB ('simple_job', force => TRUE); Также можно изменить параметры планировщика, выполняя последова- тельно приведенные далее действия: --отключить планировщик BEGIN DBMS_SCHEDULER.DISABLE ('simple_job', TRUE); END; / --изменить атрибуты BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'simple_job', attribute => 'repeat_interval', value => 'FREQ = YEARLY; BYMONTH = MAR; BYMONTHDAY = 31'); DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'simple_job', attribute => 'STOP_ON_WINDOW_CLOSE', value => TRUE); END; / --включить планировщик BEGIN DBMS_SCHEDULER.ENABLE ('simple_job'); END; / Информацию об уже созданных планировщиках можно просмотреть в таблице словаря данных USER_SCHEDULER_JOBS. Кроме того, DBMS_SCHEDULER позволяет скомпоновать задание из не- зависимых элементов: программы и расписания. Оба эти элемента самостоя- тельны и их можно комбинировать в разных заданиях и изменять, не внося из- менений в определения заданий. Пример создания программы: BEGIN DBMS_SCHEDULER.CREATE_PROGRAM (program_name => 'simple_program', program_type => 'STORED_PROCEDURE', program_action => 'updatesal', enabled => TRUE); END; / Список сведений об имеющихся программах для планировщика указан в таблице словаря USER_SCHEDULER_PROGRAMS. Другими значениями параметра PROGRAM_TYPE могут быть 'PLSQL_BLOCK' и 'EXECUTABLE'. При наличии у процедуры параметров их количество требуется указать особо: CREATE PROCEDURE salary1 (deer NUMBER ) AS BEGIN UPDATE employees SET salary = salary – deer; END; / BEGIN DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => 'simple_program1', program_type => 'STORED_PROCEDURE', program_action => 'salary1', enabled => FALSE, number_of_arguments => 1); END; / Необходимо обратить внимание, что программа создана «отключенной», потому что указать фактические значения параметров во «включенной» про- грамме нельзя, так что в дальнейшем последовательность действий будет сле- дующая: BEGIN DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT ( program_name => 'simple_program1', argument_position => 1, argument_name => 'DELTA', argument_type => 'NUMBER'); END; / BEGIN
Пример создания расписания: BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE (schedule_name => 'simple_schedule', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=WEEKLY; BYDAY=MON, TUE, WED, THU, FRI', end_date => SYSTIMESTAMP + INTERVAL '1' MONTH); END; / Список сведений об имеющихся расписаниях для планировщика нахо- дится в таблице словаря USER_SCHEDULER_SCHEDULES. В общем случае синтаксис указания графика для расписания (параметр REPEAT_INTERVAL) допускает ссылаться на ранее созданные таким же обра- зом расписания. Из самостоятельно существующих программы и расписания можно со- ставить скомпонованное задание, например: BEGIN
schedule_name => 'simple_schedule', enabled =>TRUE); END; / жүктеу/скачать 256,49 Kb. Достарыңызбен бөлісу:
|