需求产生：

业务系统中，有一步“抽数”流程，就是把一些数据从其它服务器同步到本库的目标表。这个过程有可能 多人同时抽数，互相影响。有测试人员反应，原来抽过的数，偶尔就无缘无故的找不到了，有时又会出来重复行。这个问题产生肯定是抽数逻辑问题以及并行的问题了!但他们提了一个简单的需求：想知道什么时候数据被删除了，什么时候插入了，我需要监控“表的每一次变更”!

技术选择：

***就想到触发器，这样能在不涉及业务系统的代码情况下，实现监控。触发器分为“语句级触发器”和“行级触发器”。语句级是每一个语句执行前后触发一次操作，如果我在每一个SQL语句执行后，把表名，时间，影响行写到记录表里就行了。

但问题来了，在语句触发器中，无法得到该语句的行数，sql%rowcount 在触发器里报错。只能用行级触发器去统计行数!

代码结构：

整个监控数据行的功能包含： 一个日志表，包，序列。

日志表：记录目标表名，SQL执行开始、结束时间，影响行数，监控数据行上的某些列信息。

包：主要是3个存储过程，

1. 语句开始存储过程：用关联数组来记录目标表名和开始时间，把其它值清0.
2. 行操作存储过程：把关联数组目标表所对应的记录数加1。
3. 语句结束存储过程：把关联数组目标表中统计的信息写到日志表。

   序列： 用于生成日志表的主键

   代码：

   日志表和序列：

   createtableT_CSLOG
   (
   n_idNUMBERnotnull,
   tblnameVARCHAR2(30)notnull,
   sj1DATE,
   sj2DATE,
   i_hsNUMBER,
   u_hsNUMBER,
   d_hsNUMBER,
   portcodeCLOB,
   startrqDATE,
   endrqDATE,
   bzVARCHAR2(100),
   nNUMBER
   )
   createindexIDX_T_CSLOG1onT_CSLOG(TBLNAME,SJ1,SJ2)
   altertableT_CSLOGaddconstraintPRIKEY_T_CSLOGprimarykey(N_ID)
   
   
   createsequenceSEQ_T_CSLOG
   minvalue1
   maxvalue99999999999
   startwith1
   incrementby1
   cache20
   cycle;
   

   

   包代码：

   --包头
   createorreplacepackagepck_cslogis
   --声明一个关联数组类型，它就是日志表的关联数组
   typecslog_typeistableoft_cslog%rowtypeindexbyt_cslog.tblname%type;
   --声明这个关联数组的变量。
   cslog_tblcslog_type;
   --语句开始。
   procedureonbegin_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2);
   --行操作
   procedureoneachrow_cs(v_tblnamet_cslog.tblname%type,
   v_typevarchar2,
   v_codevarchar2:='',
   v_rqdate:='');
   --语句结束，写到日志表中。
   procedureonend_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2);
   endpck_cslog;
   
   --包体
   createorreplacepackagebodypck_cslogis
   --私有方法，把关联数组中的一条记录写入库里
   procedurewrite_cslog(v_tblnamet_cslog.tblname%type)is
   begin
   ifcslog_tbl.exists(v_tblname)then
   insertintot_cslogvaluescslog_tbl(v_tblname);
   endif;
   end;
   --私有方法，清除关联数组中的一条记录
   procedureclear_cslog(v_tblnamet_cslog.tblname%type)is
   begin
   ifcslog_tbl.exists(v_tblname)then
   cslog_tbl.delete(v_tblname);
   endif;
   end;
   --某个SQL语句执行开始。v_type：语句类型，insert时为i,update时为u，delete时为d
   procedureonbegin_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2)is
   begin
   --如果关联数组中不存在，初始赋值。否则表示，同时有insert，delete语句对目标表操作。
   ifnotcslog_tbl.exists(v_tblname)then
   cslog_tbl(v_tblname).n_id:=seq_t_cslog.nextval;
   cslog_tbl(v_tblname).tblname:=v_tblname;
   cslog_tbl(v_tblname).sj1:=sysdate;
   cslog_tbl(v_tblname).sj2:=null;
   cslog_tbl(v_tblname).i_hs:=0;
   cslog_tbl(v_tblname).u_hs:=0;
   cslog_tbl(v_tblname).d_hs:=0;
   cslog_tbl(v_tblname).portcode:='';--初始给一个空格
   cslog_tbl(v_tblname).startrq:=to_date('9999','yyyy');
   cslog_tbl(v_tblname).endrq:=to_date('1900','yyyy');
   cslog_tbl(v_tblname).n:=0;
   endif;
   cslog_tbl(v_tblname).bz:=cslog_tbl(v_tblname).bz||v_type||',';
   ----***个语句进入，显示1，如果以后并行，则该值递增。
   cslog_tbl(v_tblname).n:=cslog_tbl(v_tblname).n+1;
   end;
   --每行操作。
   procedureoneachrow_cs(v_tblnamet_cslog.tblname%type,
   v_typevarchar2,
   v_codevarchar2:='',
   v_rqdate:='')is
   begin
   ifcslog_tbl.exists(v_tblname)then
   --行数，代码，起、止时间
   ifv_type='i'then
   cslog_tbl(v_tblname).i_hs:=cslog_tbl(v_tblname).i_hs+1;
   elsifv_type='u'then
   cslog_tbl(v_tblname).u_hs:=cslog_tbl(v_tblname).u_hs+1;
   elsifv_type='d'then
   cslog_tbl(v_tblname).d_hs:=cslog_tbl(v_tblname).d_hs+1;
   endif;
   
   ifv_codeisnotnulland
   instr(cslog_tbl(v_tblname).portcode,v_code)=0then
   cslog_tbl(v_tblname).portcode:=cslog_tbl(v_tblname).portcode||','||v_code;
   endif;
   
   ifv_rqisnotnullthen
   ifv_rq>cslog_tbl(v_tblname).endrqthen
   cslog_tbl(v_tblname).endrq:=v_rq;
   endif;
   ifv_rq<cslog_tbl(v_tblname).startrqthen
   cslog_tbl(v_tblname).startrq:=v_rq;
   endif;
   endif;
   endif;
   end;
   --语句结束。
   procedureonend_cs(v_tblnamet_cslog.tblname%type,v_typevarchar2)is
   begin
   ifcslog_tbl.exists(v_tblname)then
   cslog_tbl(v_tblname).bz:=cslog_tbl(v_tblname)
   .bz||'-'||v_type||',';
   --语句退出，将并行标志位减一。当它为0时，就可以写表了
   cslog_tbl(v_tblname).n:=cslog_tbl(v_tblname).n-1;
   ifcslog_tbl(v_tblname).n=0then
   cslog_tbl(v_tblname).sj2:=sysdate;
   write_cslog(v_tblname);
   clear_cslog(v_tblname);
   endif;
   endif;
   end;
   
   begin
   null;
   endpck_cslog;
   

   绑定触发器：

   有了以上代码后，想要监控的一个目标表，只需要给它添加三个触发器，调用包里对应的存储过程即可。 假定我要监控 T_A 的表：

   

   三个触发器：

   --语句开始前
   createorreplacetriggertri_onb_t_a
   beforeinsertordeleteorupdateont_a
   declare
   v_typevarchar2(1);
   begin
   ifinsertingthenv_type:='i';elsifupdatingthenv_type:='u';elsifdeletingthenv_type:='d';endif;
   pck_cslog.onbegin_cs('t_a',v_type);
   end;
   
   --语句结束后
   createorreplacetriggertri_one_t_a
   afterinsertordeleteorupdateont_a
   declare
   v_typevarchar2(1);
   begin
   ifinsertingthenv_type:='i';elsifupdatingthenv_type:='u';elsifdeletingthenv_type:='d';endif;
   pck_cslog.onend_cs('t_a',v_type);
   end;
   
   --行级触发器
   createorreplacetriggertri_onr_t_a
   afterinsertordeleteorupdateont_a
   foreachrow
   declare
   v_typevarchar2(1);
   begin
   ifinsertingthenv_type:='i';elsifupdatingthenv_type:='u';elsifdeletingthenv_type:='d';endif;
   ifv_type='i'orv_type='u'then
   pck_cslog.oneachrow_cs('t_a',v_type,:new.name);--此处是把监控的行的某一列的值传入包体，这样***会记录到日志表
   elsifv_type='d'then
   pck_cslog.oneachrow_cs('t_a',v_type,:old.name);
   endif;
   end;
   

   测试成果：

   触发器建好了，可以测试插入删除了。先插入100行，再随便删除一些行。

   declare
   inumber;
   begin
   foriin1..100loop
   insertintot_avalues(i,i||'shenjunjian');
   endloop;
   commit;
   
   deletefromt_awhereid>79;
   deletefromt_awhereid<40;
   commit;
   end;
   

   

   clob列，还可以显示监控删除的行：

   

   并行时，在bz列中，可能会有类似信息：

   i,i,-i,-i ,这表示同一时间有2个语句在插入目标表。

   i,d,-d,-i 表示在插入时，有一个删除语句也在执行。

   当平台多人在用时，避免不了有同时操作同一张表的情况，通过这个列的值，可以观察到数据库的执行情况!