DML_monitor

最新推荐文章于 2021-04-11 10:17:52 发布

转载最新推荐文章于 2021-04-11 10:17:52 发布 · 95 阅读

本文详细介绍如何在Oracle数据库中使用触发器来跟踪特定表的数据变更，包括删除、插入和更新操作，通过实例展示了如何创建触发器以记录操作员、时间戳及具体变更类型。

--跟踪某张表中数据的删除、新增、修改等过程：谁什么时候删的(能记录到IP，这点挺好)
--主要trigger的方式记录表数据的删除，再用sys_context取到操作用户的相关信息

--建立目标表，后边会记录所有基于该表的DML操作
create table zxt_test01
(
id1 varchar2(10),
id2 varchar2(10)
);

--建立记录跟踪表，用来记录相关信息
create table zxt_test01_trigger
(
USER_HOST varchar2(60),
USER_IP varchar2(20),
OS_user varchar2(30),
TERMINAL varchar2(30),
db_user varchar2(30),
session_id varchar2(20),
operate_time date,
tablename varchar2(40),
operate_type varchar2(10)
);

--建立基于目标表的触发器
create or replace trigger trig_af_de_zxt_test01
after delete on sb_bf_grgzxx
for each row
declare
-- local variables here
v_sessionid varchar2(20);
begin
select t.SID into v_sessionid from v$session t where t.AUDSID=sys_context('USERENV','SESSIONID');
insert into zxt_test01_trigger values(
sys_context('USERENV','HOST'),
sys_context('USERENV','IP_ADDRESS'),
sys_context('USERENV','OS_USER'),
sys_context('USERENV','TERMINAL'),
sys_context('USERENV','CURRENT_USER'),
v_sessionid,
sysdate,
'zxt_test01',
'DELETE'
);
end trig_af_de_zxt_test01;

---下面测试一下
insert into zxt_test01 varlus (11,21);
insert into zxt_test01 varlus (12,22);
insert into zxt_test01 varlus (13,23);
commit;

delete from zxt_test01 where id1='11';
delete from zxt_test01 where id1='21';
delete from zxt_test01 where id1='31';
commit;

SQL> select * from zxt_test01_trigger;

USER_HOST                 USER_IP       OS_USER        TERMINAL        DB_USER SESSION_ID OPERATE_TIME TABLENAME OPERATE_TYPE
------------------------- ------------- -------------- --------------- -------- ----------- ------------ ---------- ------------
WORKGROUP\LENOVO-B6EE524D 152.16.16.26 Administrator LENOVO-B6EE524D ZXT      102         2011-12-9 8: zxt_test01 DELETE
WORKGROUP\ZXT             152.16.23.151 Administrator ZXT             ZXT      102         2011-12-9 8: zxt_test01 DELETE
WORKGROUP\ZXT             152.16.23.151 Administrator ZXT             ZXT      193         2011-12-9 9: zxt_test01 DELETE

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/694276/viewspace-713030/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/694276/viewspace-713030/