, CONSTRAINT scl pk PRIbiARY KEY (emp#, transaction dt)

CREATE OR REPLACE TRIGGER pay aur AFTER UPDATE ON payroll

FOR EACH ROW

BEGIN

IF :old.erap# <> :new.erap#

THEN RAISE APPLICATION ERROR(-2000,

Illegal primary key cange attempted on table PAYROLL); END IF;

IF -.old.salary <> -.new.salary THEN BEGIN

INSERT INTO salary change log ( emp#

, transaction dt

, user narae

, old sal value

, new sal value

) VALUES

( :new.erap no

, SYSDATE

, USER

, :old.salary , :new.salary ) ;

END; END IF; END pay aur;

Теперь, если кто-нибудь попытается изменить зарплату до или после вызова программы начисления, наш триггер должен отловить эту попытку. Но вот отловит ли? Альтернатива обновлению строки - ее удаление и повторная вставка, и, действительно, это будет делаться, если пользователь захочет изменить номер служащего. К сожалению, мы не перехватываем удаления и вставки, а, вероятно, следовало бы. В примере 10.1 приведены определение таблицы аудита для зарплаты и новый триггер, перехватывающий все три события.

Если вы не пожалеете времени и разберете этот пример, то увидите, что мы используем один триггер, который проверяет наличие вставки, обновления и удаления, а не отдельный триггер для каждого события. Существуют разные мнения по поводу того, какой метод лучше. Мы предпочитаем размещать реализацию правил аудита для таблицы в одном (и только одном) месте.

Наш пример можно усовершенствовать. В частности, многие аудиторы попросят, чтобы записи аудита имели порядковые номера по служащим. Это позволит установить, какие записи удалены. Здесь есть два момента: во-первых, необходимо подумать, как реализовать это требование, и, во-вторых, выяснить, почему аудиторы считают, что кто-то, знающий о системе достаточно для того, чтобы удалить записи аудита, не сможет изменнггь в меньшую сторону даты последующих записей. Пока мы займемся только первым из Этих вопросов и пронумеруем записи. В результате первичный ключ будет

выглядеть так: (ЕМР#, CHANGE*), и номера изменений будут начинаться с единицы (или с нуля) и непрерывно возрастать.

Пример 10.1. Таблица аудита и триггер для сбора информации

CREATE TABLE salary change log (emp# NUMBER(10) NOT NULL

,transaction dt DATE NOT NULL

,user name VARCHAR2(30) NOT NULL

,dml type CHAR(l) NOT NULL

,CONSTRAINT scl dml type CHECK (dml type IN (I, U, D)) ,old sal value NUMBER (8,2) ,CONSTRAINT scl old sal

CHECK ( DECODE(dml type, I, 0, U, 1, D, 1, 0) = DECODE(old sal value, null, 0, 1) ,new sal value NUMBER (8,2) ,CONSTRAINT scl new sal

CHECK ( DECODE(dml type, I, 1, U, 1, D, 0, 0) = DECODE(new sal value, null, 0, 1) ,CONSTRAINT scl pk PRIMARY KEY (emp#, transaction dt) ) ;

CREATE OR REPLACE TRIGGER pay aud

AFTER INSERT OR UPDATE OR DELETE ON payroll

FOR EACH ROW

DECLARE

l dml typ salary change log.dml type%TYPE; BEGIN

IF .-old-empt <> . new.empt - ошибка, если THEN RAISE APPLICATION ERROR(-2 000,

Illegal primary key change attempted on table PAYROLL); END IF;

IF UPDATING (SALARY) THEN

l dml type := U; ELSIF INSERTING THEN

l dml type := I; ELSE - удаление

l dml type := D; END IF;

IF l dml typ IS NOT NULL

THEN INSERT INTO salary change log ( emp*

, transaction dt

, user name

, dml type

, old sal value

, new sal value

) VALUES

( . new.emp no

, SYSDATE

, USER

, l dml typ

, :old.salary

, :new.salary

END IF; END pay aud;

Мы добавляем в таблицу PAYROLL столбец, содержащий максимальный номер изменения для каждого сотрудника. Затем, когда потребуется сделать запись аудита, мы прочитаем элемент таблицы PAYROLL для данного сотрудника, получим текущий номер, прибавим к нему единицу, обновим таблицу PAYROLL и вставим новую строку в таблицу аудита. Однако при этом проявится проблема мутирующих таблиц, способ борьбы с которой описан в приложении Б.

На данном этапе мы уверены, что продемонстрировали аудиторам свою способность регистрировать все операции, которые выполняются со строкой (за исключением, конечно, запросов к ней). Но предела их желаниям нет, и у них возникает выдающаяся идея: мы должны расширить сферу аудита и обеспечить регистрацию всех изменений всех столбцов всех таблиц. Как вам это нравится? В этом случае нам не только придется задействовать огромный объем пространства БД для хранения этих данных и ресурсы центрального процессора на создание записей журнала аудита, но и написать (и протестировать) огромные объемы триггерного кода. (У нас ушло достаточно много времени на отладку триггера pay aud в примере 10. L а ведь оба мы имеем значительный опыт проектирования и написания триггеров для задач аудита.)

По этому поводу мы рекомендуем следующее:

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

2. Не пытайтесь использовать всего одну таблицу аудита. SQL вообще плохо обрабатывает метаструктуры, и гораздо проще иметь отдельную таблицу аудита для каждой таблицы. Если есть две таблицы с одинаковыми определениями столбцов (секционированные данные), то при необходимости они могут совместно использовать одну таблицу аудита.

3. Для создания триггеров аудита используйте генератор. Если по какой-то причине их нужно исправить, не пытайтесь корректировать сгенерированный код, а исправьте генератор и постройте триггеры заново. Это один из случаев, когда вы можете выбрать легкий путь (читая нашу книгу и следуя нашим советам) или трудный путь (и затем объяснять своему начальнику, почему простое изменение в спецификации повлекло за собой двухлетнее отставание от графика).

Альтернативный подход

Достаточно жизнеспособная альтернатива созданию журналов аудита для обновлений и удалений заключается в том, чтобы никогда не изменять Никакие данные (т.е. не обновлять базовые данные) и никогда не удалять строки, кроме как в операциях архивации. Приняв эти два правила (и введя их триггерами), вы никогда не потеряете данные и не пропустите промежуточное состояние, которое существовало, может быть, всего несколько часов.

Как же такое возможно? Один из способов реализации этого метода состоит в хранении производных записей. Так, в нашем примере с зарплатой может существовать производная запись (ведомая только триггерами).