Автономные транзакции 1167

В стандартной таблице ЕМР сотрудник ADAMS подчиняется сотруднику SCO, поэтому первый оператор UPDATE выполняется успешно. Второе изменение (пользователь SCO пытается поднять самому себе зарплату) не выполняется, поскольку SCOTT не является своим руководителем. Если снова зарегистрироваться от имени пользователя, которому принадлежит таблица AUDIT TAB, можно увидеть следующее:

scott@TKYTE816> connect tkyte/tkyte tkyte@TKYTE816> select * from audit tab;

USER NAME TIMESTAMP MSG

TKYTE 15-APR-01 Attempt to update 7369

SCOTT 15-APR-01 Attempt to update 7788

Попытка изменения данных пользователем SCO зарегистрирована в этой таблице. Осталось разобраться, почему так важно, что триггер по таблице ЕМР читает дан-н1е из таблице! ЕМР? Это рассматривается в следующем разделе.

Метод, позволяющий избежать ошибки изменяющейся таблицы

Ошибка изменяющейся таблицы (mutating table error) может возникнуть по нескольким причинам. Чаще всего она возникает при попытке читать данные из таблицы, в ответ на изменение которой сработал триггер. В представленном выше примере мы явно читали данные из таблицы, изменение которой вызвало срабатывание триггера. Закомментируем две строки в тексте триггера и попытаемся использовать его следующим образом:

tkyte@TKYTE816> create or replace trigger EMP AUDIT

2 before update on emp

3 for each row

4 declare

5 - pragma autonomous transaction;

6 l cnt number;

7 begin 8

9 select count(*) into l cnt

10 from dual

11 where EXISTS (select null

12 from emp

13 where empno = :new.empno

14 start with mgr = (select empno

15 from emp

16 where ename = USER)

17 connect by prior empno = mgr); 18

19 if ( l cnt = 0 )

20 then

21 insert into audit tab (msg)

22 values (Attempt to update :new.empno);

23 - commit;

1168

Глава 15

25 raise application error(-20001, Access Denied);

26 end if ; 2 7 end;

28 /

tkyte@TKYTE816> update emp set sal = sal*10; update emp set sal = sal*10

ERROR at line 1:

ORA-04091: table TKYTE.EMP is mutating, trigger/function may not see it

ORA-06512: at TKYTE.EMP AUDIT , line 6

ORA-04088: error during execution of trigger TKYTE.EMP AUDIT

Без использования автономных транзакций представленный выше триггер написать сложно, даже если он всего лишь пытается проверить, имеет ли право пользователь изменять данную строку (и не пытается зарегистрировать эту попытку). До появления прагмы AUTONOMOUS TRANSACTION для этого необходимо было создать пакет и три триггера. Это не значит, что во избежание ошибок изменяющейся таблицы во всех случаях следует использовать автономные транзакции - их надо использовать осторожно и четко представлять себе, как обрабатываются транзакции. В разделе Проблемы я объясню это подробнее. Ошибка изменяющейся таблицы призвана защитить целостность данных, и очень важно понимать, почему она возникает. Не обманывайте себя: автономные транзакции не устраняют ошибку изменяющейся таблицы в триггерах раз и навсегда!

Выполнение операторов ЯОД в триггерах

Часто задают вопрос: Как создать объект базы данных при вставке строки в такую-то таблицу? . При этом спрашивают о разных объектах. Иногда хотят создать пользователя базы данных при вставке строки в таблицу, иногда - таблицу или последовательность. Поскольку при выполнении операторов ЯОД непосредственно перед самим оператором и сразу после него выполняется фиксация транзакции (или фиксация и откат, если при выполнении оператора ЯОД произошла ошибка), выполнять эти операторы в триггере было невозможно. Автономные транзакции теперь позволяют это делать.

Раньше приходилось использовать пакет DBMS JOB для выполнения задания с соответствующими операторами ЯОД после фиксации транзакции. Это решение по-прежнему возможно, и почти всегда оно является корректн1м и оптпмальным. Использование подпрограмм пакета DBMS JOB для выполнения оператора ЯОД в виде отдельного задания хорошо тем, что позволяет включить операторы ЯОД в транзакцию. Если триггер поставил задание на выполнение, и это задание создало учетную запись пользователя, при откате родительской транзакции поставленное в очередь задание по созданию учетной записи пользователя тоже будет отменено. Строка, представляющая задание, будет удалена . Не останется ни записи в таблице, ни учетной записи в системе. Если в этом случае использовать автономные транзакции, учетная запись в базе данных будет создана, а записи в таблице не будет. Недостатком подхода на базе пакета DBMS JOB является неизбежное небольшое отставание между моментом фиксации транзакции и запуском задания. Учетная запись пользователя будет создана вскоре пос-

Автономные транзакции 1169

ле фиксации, но не сразу. В зависимости от требований, можно использовать тот или иной метод. Повторю еще раз, что почти в любом случае можно найти аргументы в пользу пакета DBMS JOB.

В качестве примера выполнения операторов ЯОД в триггере рассмотрим ситуацию, когда необходимо создавать учетную запись пользователя базы данных при вставке строки в таблицу и удалять эту запись при удалении соответствующей строки. В представленном ниже примере я буду использовать свой способ, позволяющий избежать ситуаций, когда учетная запись пользователя создана, а строки в таблице нет, или строка в таблице осталась, а учетная запись пользователя удалена. Этот способ основан на использовании триггера INSTEAD OF для представления таблицы APPLICATION USERS TBL.

Триггеры INSTEAD OF - удобное средство, позволяющее задать действия, выполняе-м1е при изменении строк представления вместо стандартных действий сервера Oracle. В главе 20, посвященной использованию объектно-реляционных средств, будет продемонстрировано, как с помощью триггеров INSTEAD OF обеспечить изменение слож-н1х представлений, которые сервер Oracle обычно изменять не позволяет. Мы будем использовать эти триггеры для создания учетной записи пользователя и вставке строки в реальную таблицу (или удаления учетной записи пользователя и соответствующей строки таблицы). Этот метод гарантирует, что либо создана учетная запись и строка вставлена, либо ни то, ни другое не выполнено. Если бы триггер был создан только по самой таблице, мы не могли бы этого гарантировать, а вот представление поможет нам связать эти два события. Вместо вставки и удаления данных из реальной физической таблицы все приложения будут вставлять и удалять данные из представления. Для представления будет создан триггер INSTEAD OF, так что все изменения можно будет выполнить последовательно, в процедурном коде. Это позволит гарантировать, что если строка существует в реальной таблице, то и учетная запись пользователя тоже создана. Если строка удалена из реальной таблицы, то удалена и учетная запись. Как этого добиться, лучше всего продемонстрирует пример. Я буду объяснять существенные детали, как только мы до них доберемся.

Начнем с создания схемы, в которой будут храниться объекты приложения:

tkyte@TKYTE816> create user demo ddl identified by demo ddl; User created.

tkyte8TKYTE816> grant connect, resource to demo ddl with admin option; Grant succeeded.

tkyte@TKYTE816> grant create user to demo ddl; Grant succeeded.

tkyte@TKYTE816> grant drop user to demo ddl; Grant succeeded.

tkyte@TKYTE816> connect demo ddl/demo ddl demo ddl@TKYTE816>

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