[事务相关] 基于自治事务审计

-- 如果在一个“正常”的过程中COMMIT，它不仅会持久保留自己的工作，也会使该会话中未完成的工作成为永久性的
-- 不过，如果在一个自治事务过程中完成COMMIT，只会让这个过程本身的工作成为永久性的

-- 测试表
go@DEMO> create table emp as select * from scott.emp;

Table created.

go@DEMO> grant all on emp to scott;

Grant succeeded.

-- 审计记录表
go@DEMO> create table audit_tab
(
    username varchar2(30) default user,
    timestamp date default sysdate,
    msg varchar2(4000)
)
/

Table created.

-- 触发器
-- 保证只有等级高的人才能修改等级低的人的记录
go@DEMO> create or replace trigger emp_audit
before update on emp for each row
declare
    pragma autonomous_transaction;
    l_cnt number;
begin
    select count(*) into l_cnt from dual
    where exists
    (
        select null from emp where empno = :new.empno
        start with mgr =
            (
                select empno from emp where ename = USER
            )
        connect by prior empno = mgr
    );
if(l_cnt = 0)
then
    insert into audit_tab (msg) values ('Attempt to update ' || :new.empno);
    commit;

raise_application_error(-20001, 'Access Denied');
end if;
end;
/

Trigger created.

-- 更新所有人工资
go@DEMO> update emp set sal = sal*10;
update emp set sal = sal*10
       *
ERROR at line 1:
ORA-20001: Access Denied
ORA-06512: at "GO.EMP_AUDIT", line 20
ORA-04088: error during execution of trigger 'GO.EMP_AUDIT'

go@DEMO> column msg format a40
go@DEMO> select * from audit_tab;
USERNAME        TIMESTAMP           MSG
--------------- ------------------- ----------------------------------------
GO              2011-08-02 20:41:02 Attempt to update 7369

1 row selected.

go@DEMO> conn scott/tiger

Session altered.

-- 更新属下工资
scott@DEMO> update go.emp set sal = sal*1.05 where ename = 'ADAMS';

1 row updated.

scott@DEMO> commit;

Commit complete.

-- 更新自己工资
scott@DEMO> update go.emp set sal = sal*1.05 where ename = 'SCOTT';
update go.emp set sal = sal*1.05 where ename = 'SCOTT'
          *
ERROR at line 1:
ORA-20001: Access Denied
ORA-06512: at "GO.EMP_AUDIT", line 20
ORA-04088: error during execution of trigger 'GO.EMP_AUDIT'


scott@DEMO> conn go/go

Session altered.

go@DEMO> select * from audit_tab;
USERNAME        TIMESTAMP           MSG
--------------- ------------------- ----------------------------------------
GO              2011-08-02 20:41:02 Attempt to update 7369
SCOTT           2011-08-02 20:43:45 Attempt to update 7788

2 rows selected.