如果一张表非常重要需要审计失败的操作.自治事务可以帮我们完成.我们可通过自治事务建立两个before/after trigger，如果操作成功before/alter是配对的.不成功则只有before。

SQL> l
1 create or replace trigger trg_aud_bef_emp
2 before insert on emp for each row
3 declare
4 pragma autonomous_transaction;
5 begin
6 insert into audit_emp values(:new.ename,'before insert',sysdate);
7 commit;
8 end;
9*
SQL> /

Trigger created.

SQL> desc emp
Name Null? Type
----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> insert into emp(empno,ename,detpno) values(11,'jerry',20);
insert into emp(empno,ename,detpno) values(11,'jerry',20)
*
ERROR at line 1:
ORA-00904: "DETPNO": invalid identifier

SQL> c/tp/pt
1* insert into emp(empno,ename,deptno) values(11,'jerry',20)
SQL> /

1 row created.

SQL> commit
2 ;

Commit complete.

SQL> select * from audit_emp;

UNAME OPER TM
------------------------------ -------------------- ---------
jerry before insert 05-APR-11

SQL> insert into emp(empno,ename,deptno) values(11,'jerry',12);
insert into emp(empno,ename,deptno) values(11,'jerry',12)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated

SQL> select * from audit_emp;

UNAME OPER TM
------------------------------ -------------------- ---------
jerry before insert 05-APR-11
jerry before insert 05-APR-11

SQL> create or replace trigger trg_aud_aft_emp
after insert on emp for each row
declare
pragma autonomous_transaction;
begin
insert into audit_emp values(:new.ename,'after insert',sysdate);
commit;
end; 2 3 4 5 6 7 8
9 /

Trigger created.

SQL> insert into emp(empno,ename,deptno) values(12,'jack',30);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from audit_emp;

UNAME OPER TM
------------------------------ -------------------- ---------
jerry before insert 05-APR-11
jerry before insert 05-APR-11
jack before insert 05-APR-11
jack after insert 05-APR-11

SQL> insert into emp(empno,ename,deptno) values(12,'jack1',30);
insert into emp(empno,ename,deptno) values(12,'jack1',30)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated

SQL> select * from audit_emp;

UNAME OPER TM
------------------------------ -------------------- ---------
jerry before insert 05-APR-11
jerry before insert 05-APR-11
jack before insert 05-APR-11
jack after insert 05-APR-11
jack1 before insert 05-APR-11

[@more@]