Ошибки при изменении таблицы. Довольно часто при разработке триггеров на изменение данных в таблице приходится сталкиваться с проблемой «мута- ции» триггеров, а именно с ошибкой 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
|
Создание объекта
|
ALTER
|
Изменение объекта
|
DROP
|
Удаление объекта
|
ANALYZE
|
Сбор статистики по объекту
|
ASSOCIATE STATISTICS
|
Связывание статистики с объектом
|
AUDIT
|
Включение средств аудита
|
NOAUDIT
|
Отключение средств аудита
|
COMMENT ON
|
Создание комментария на объект
|
DDL
|
Наступление любого из DDL-событий
|
DIASSOCIATE STATISTICS
|
Удаление статистики
|
GRANT
|
Назначение прав пользователю
|
RENAME
|
Переименование объекта
|
REVOKE
|
Отмена прав пользователю
|
TRUNCATE
|
Очистка таблицы
|
Применение триггеров позволяет отслеживать, фиксировать, проверять, запрещать действия с объектами всей базы данных или схемы пользователя. Например:
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
Список событий триггера уровня БД
Событие
|
Описание
|
SERVERERROR
|
Определяет триггер, срабатывающий в момент, когда Ora- cle генерирует какую-либо ошибку. Исключения составля- ют следующие сообщения: ORA-01403: data not found
ORA-01422: exact fetch returns more than requested number of rows; ORA-01423: error encountered while checking for ex-
tra rows in exact fetch;ORA-01034: ORACLE not available; ORA-04030: out of process memory
|
LOGON
|
Определяет триггер на событие LOGON – начало сеанса
|
LOGOFF
|
Определяет триггер на событие LOGOFF – закрытия сес-
сии клиента
|
STARTUP
|
Определяет триггер на событие STARTUP – старт базы
данных
|
SHUTDOWN
|
Определяет триггер на событие SHUTDOWN – закрытие
базы данных
|
SUSPEND
|
Определяет триггер на событие «приостановление ин-
струкции» (появился в версии Oracle 9i)
|
Необходимо отметить, что системные триггеры уровня БД (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
DBMS_SCHEDULER.ENABLE ('simple_program1'); END;
Пример создания расписания:
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
DBMS_SCHEDULER.CREATE_JOB ( job_name => 'compound_job', program_name => 'simple_program',
schedule_name => 'simple_schedule', enabled =>TRUE);
END;
/
Достарыңызбен бөлісу: |