用于记载DML操作所引起的数据变化,通过触发器来实现。
(1)建立审计表(用来存放审计结果)
11:37:32 SQL> conn scott/tiger
Connected.
create table audit_emp_change (
04:20:47 2 name varchar2(10),oldsal number(6,2),
04:21:12 3 newsal number(6,2) ,time date);
Table created.
(2)建立DML 触发器
04:26:47 SQL> l
1 create or replace trigger tr_sal_change
2 after update of sal on scott.emp ——触发时间
3 for each row
4 declare
5 v_temp int;
6 begin
7 select count(*) into v_temp from audit_emp_change
8 where name=:old.ename;
9 if v_temp=0 then
10 insert into audit_emp_change
11 values(:old.ename,:old.sal,:new.sal,sysdate);
12 else
13 update audit_emp_change
14 set oldsal=:old.sal ,newsal=:new.sal ,time=sysdate
15 where name=:old.ename;
16 end if;
17* end;
/
(3)执行DML操作
04:28:02 SQL> update scott.emp set sal=6000 where empno=7788;
1 row updated.
(4)查看审计结果
04:28:35 SQL> select name,oldsal,newsal,
04:28:46 2 to_char(time,'YYYY-MM-DD HH24:MI') FROM AUDIT_EMP_CHANGE;
NAME OLDSAL NEWSAL TO_CHAR(TIME,'YY
---------- ---------- ---------- ----------------
SCOTT 2000 6000 2011-03-03 04:28
更多oracle视频教程:http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6
转载于:https://blog.51cto.com/19880614/1175218