| ORA-04092与自治事物 | DBA之路：OoNiceDream |

ORA-04092与自治事物

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/08/ora_4092_autonomous_transaction.html

用登陆触发器实现用户名和IP的限制，要记录一些日志信息，带了insert、 commit之类的语句，测试时报了：

ORA-04092: cannot COMMIT in a trigger

不能在trigger中使用commit。
例如：

SQL> create or replace trigger tr_delete
  2  after delete on rockey.test
  3  for each row
  4  DECLARE
  5  
  6  begin
  7   insert into l_date values(sysdate);
  8   commit;
  9  end;
 10  /
 
Trigger created.
 
SQL> delete from rockey.test;
delete from rockey.test
                   *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.TR_DELETE", line 5
ORA-04088: error during execution of trigger 'SYS.TR_DELETE'

采用自治事物，可以避免该报错：

SQL> create or replace trigger tr_delete
  2  after delete on rockey.test
  3  for each row
  4  DECLARE
  5   pragma AUTONOMOUS_TRANSACTION;
  6  begin
  7   insert into l_date values(sysdate);
  8   commit;
  9  end;
 10  /
 
Trigger created.
 
SQL> select * from rockey.test;
 
        ID
----------
         1
 
SQL> delete from rockey.test;
 
1 row deleted.
 
SQL> select * from l_date;
 
A
---------
21-AUG-09
 
SQL> rollback;
 
Rollback complete.
 
SQL> select * from l_date;
 
A
---------
21-AUG-09
 
SQL> select * from rockey.test;
 
        ID
----------
         1

这个例子中，使用自治事物后，自治事物的提交与当前事物的回滚已经互不影响了。

自治事物(AT)，是一个独立执行的事物，自治事物的提交或回滚都不会影响当前事物的状态。
例如，以下这个PL/SQL块中，未使用自治事物，则该块中的事物的提交、回滚就会影响到当前事物：

SQL> select * from test;
 
no rows selected
 
SQL> insert into test values(1);
 
1 row created.
 
SQL> insert into test values(2);
 
1 row created.
 
SQL> begin
  2  insert into test values(3);
  3  commit;
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
SQL> rollback;
 
Rollback complete.
 
SQL> select * from test;
 
         X
----------
         1
         2
         3

定义了自治事物以后，内部事物的提交、回滚不再影响当前事物：

SQL> truncate table test;
 
Table truncated.
 
SQL> insert into test values(1);
 
1 row created.
 
SQL> insert into test values(2);
 
1 row created.
 
SQL> declare
  2  pragma AUTONOMOUS_TRANSACTION;
  3  begin
  4  insert into test values(3);
  5  commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
SQL> rollback;
 
Rollback complete.
 
SQL> select * from test;
 
         X
----------
         3