DML_monitor

ITPUB个人空间zJj@A"Y*I|x"M
--跟踪某张表中数据的删除、新增、修改等过程：谁什么时候删的(能记录到IP，这点挺好)
+aC i:em*O&N*Z v0--主要trigger的方式记录表数据的删除，再用sys_context取到操作用户的相关信息

--建立目标表，后边会记录所有基于该表的DML操作
"K{%\k/m v0create table zxt_test01
"Ld+y&]5r/QNE.o0(ITPUB个人空间E1gF4S+J8L
id1 varchar2(10),ITPUB个人空间a"bS+G$x)j}
id2 varchar2(10)ITPUB个人空间` {A%|.^.^7{5qr L
);

--建立记录跟踪表，用来记录相关信息
:}T7]
GUZ0create table zxt_test01_triggerITPUB个人空间^(GJ*hlP.\-j~
(
w`3y"n'?&n Sj"\lj&e0USER_HOST varchar2(60),
edfed1ej+F0USER_IP   varchar2(20),ITPUB个人空间TS OJ$?n I7\
OS_user varchar2(30),
?(F!EF+w i#{7``0TERMINAL varchar2(30),ITPUB个人空间%pu)GmcrV1t
db_user varchar2(30),ITPUB个人空间BL uKQo@Mm
session_id varchar2(20),ITPUB个人空间*u YK&[#s a^M8M
operate_time date,
L`7WT9I}J0tablename    varchar2(40),
.I7u TZT/m+T0operate_type varchar2(10)

[
HH9s:y4XP6xB{)k0);

--建立基于目标表的触发器ITPUB个人空间?5B s9m4n"w
create or replace trigger trig_af_de_zxt_test01
v:o#o:K!wG0  after delete on sb_bf_grgzxx 
S'z:k/SfI@TK c0  for each rowITPUB个人空间[^4{ Y c1o
declare
~weeZf-Qx1a0  -- local variables here
.q)^I$E0`$Zw3x6m i0  v_sessionid varchar2(20);
1KJ,]1Zg0begin
1jW9\|.?0TCuDV0  select t.SID into v_sessionid from v$session t where t.AUDSID=sys_context('USERENV','SESSIONID');
#~7]i9Vc2I0  insert into zxt_test01_trigger values(
"P@*u"rQ;U2z0  sys_context('USERENV','HOST'),ITPUB个人空间6J~+v)x3L3k
  sys_context('USERENV','IP_ADDRESS'),
e1y:rO\H2h9D0  sys_context('USERENV','OS_USER'),ITPUB个人空间V#g`Vy7n
  sys_context('USERENV','TERMINAL'),ITPUB个人空间P B!_.D2{8\
  sys_context('USERENV','CURRENT_USER'),ITPUB个人空间D)mf0^TP.u W
  v_sessionid,ITPUB个人空间W*M!SD^
  sysdate,ITPUB个人空间\6k5a7eC^
  'zxt_test01',ITPUB个人空间,RC ^V/t$d
  'DELETE'
&Zc]w6QC0  );
)x6h$?UGg}5t0end trig_af_de_zxt_test01;

---下面测试一下ITPUB个人空间)K*g|m(R*c
insert into zxt_test01 varlus (11,21);ITPUB个人空间7i [*Z$Ar8r
insert into zxt_test01 varlus (12,22);ITPUB个人空间bt3?2y.Z _C
insert into zxt_test01 varlus (13,23);
$[$x~t.a4T/?:C0commit;

delete from zxt_test01 where id1='11';
wSY{4@6c3G&U.\*e U0delete from zxt_test01 where id1='21';ITPUB个人空间 O`#c X%^0F*{
delete from zxt_test01 where id1='31';ITPUB个人空间Va9\k%R6W s
commit;

SQL> select * from zxt_test01_trigger;ITPUB个人空间K,N j5htgA
 ITPUB个人空间xd(M{i,h*KT
USER_HOST                 USER_IP       OS_USER        TERMINAL        DB_USER  SESSION_ID  OPERATE_TIME TABLENAME  OPERATE_TYPE
4t6A/`F fv&d:^4U0------------------------- ------------- -------------- --------------- -------- ----------- ------------ ---------- ------------ITPUB个人空间\1s#Py@Rsmm
WORKGROUP\LENOVO-B6EE524D 152.16.16.26  Administrator  LENOVO-B6EE524D ZXT      102         2011-12-9 8: zxt_test01 DELETE
r7@ Y-Fm:Q0WORKGROUP\ZXT             152.16.23.151 Administrator  ZXT             ZXT      102         2011-12-9 8: zxt_test01 DELETEITPUB个人空间0QB2RV'i.mDsu
WORKGROUP\ZXT             152.16.23.151 Administrator  ZXT             ZXT      193         2011-12-9 9: zxt_test01 DELETE
R
m$h8|3P;dR9j-L0 ITPUB个人空间:wI&sGQ1U
SQL>
pY0?:f!\m]XE@
s0