给指定的一批表自动加DML触发器

为了跟踪一批表的操作记录,需要对该批表做触发器进行跟踪(没有用审计,因为审计需要有SYS权限),通过一个一个表写触发器,显然不太现实,

Oracle又不支持同时对多个表建立一个触发器.这里我就用存储过程的方式实现自动创建触发器了.

注:本人还是不建议使用大量触发器的,因为大量触发器会影响性能,而且据说触发器是串行操作的,那么速度上肯定会打折扣,还有一个原因就是触发器容易造成不能预知的递归触发。

一、首先我们创建日志记录表(某位大神的话,触发器一般只用作日志记录就好)

--创建DML日志记录表
CREATE TABLE dml_log
(
session_id number(22) NOT NULL,
oper_user varchar2(100) ,
tabname  varchar2(50),
oper_type varchar2(50),
server_host varchar2(200),
ip VARCHAR2(20),
run_program varchar2(200),
oper_date date
);
-- Add comments to the columns 
comment on column dml_log.session_id is '当前会话ID';
comment on column dml_log.oper_user is '操作系统用户名';
comment on column dml_log.tabname is '修改的表名';
comment on column dml_log.oper_type is '操作类型';
comment on column dml_log.run_program is '连接数据库使用的应用程序';
comment on column dml_log.server_host is '操作客户端的电脑名称';
comment on column dml_log.ip is '操作客户端的IP地址';
comment on column dml_log.oper_date is '修改的日期';

二、给要加触发器的表的拥有者给创建触发器的权限

15:06:08 SYS@orcl> GRANT CREATE  TRIGGER TO SCOTT;

授权成功。

三、写自动创建的存储过程

  --创建存储自动创建对应表的触发器
  create or replace procedure autocreate_trigger(ptable varchar2) --ptable格式:'EMP,EMP_BAK...'
 as
  /*
  描述:    自动给指定的表创建DML触发器,并将DML操作记录写到日志表DML_LOG
  参数:    ptable --表名,格式为'EMP,EMP_BAK...'
  AUTHOR   DATE(YYYY.MM.DD)
  Cryking  2013.4.8   --Created
  */
  V_SQL VARCHAR2(20000);
  TYPE TYPE_TABLENAME IS TABLE OF clob INDEX BY BINARY_INTEGER;
  V_TABNAME TYPE_TABLENAME;
  --自动批量创建触发器
begin
  SELECT * BULK COLLECT INTO V_TABNAME FROM TABLE(SPLITSTR(ptable, ',')); --用逗号分隔各个表变量
  for x in 1 .. V_TABNAME.count
  loop
    V_SQL := 'CREATE OR REPLACE TRIGGER TRI_' || V_TABNAME(X) || CHR(10) ||
             'AFTER INSERT OR UPDATE OR DELETE ON ' || V_TABNAME(X) ||
             CHR(10) || 'FOR EACH ROW' || CHR(10) || 'DECLARE ' || CHR(10) ||
             'V_SESSION V$SESSION.AUDSID%TYPE;' || CHR(10) ||
             'V_PROGRAM V$SESSION.PROGRAM%TYPE;' || CHR(10) || 'BEGIN' ||
             CHR(10) ||
             'SELECT userenv(''sessionid'') INTO V_SESSION FROM DUAL;' ||
             CHR(10) ||
             'SELECT PROGRAM INTO V_PROGRAM FROM V$SESSION WHERE AUDSID=userenv(''sessionid'');' ||
             CHR(10) || 'CASE WHEN inserting THEN ' || CHR(10) ||
             'INSERT INTO dml_log VALUES(V_SESSION,sys_context(''userenv'', ''OS_USER''),''' ||
             V_TABNAME(X) ||
             ''',''INSERT'',sys_context(''USERENV'',''HOST''),sys_context(''USERENV'',''IP_ADDRESS''),V_PROGRAM,SYSDATE );' ||
             CHR(10) || 'WHEN updating THEN ' || CHR(10) ||
             'INSERT INTO dml_log VALUES(V_SESSION,sys_context(''userenv'', ''OS_USER''),''' ||
             V_TABNAME(X) ||
             ''',''UPDATE'',sys_context(''USERENV'',''HOST''),sys_context(''USERENV'',''IP_ADDRESS''),V_PROGRAM,SYSDATE );' ||
             CHR(10) || 'WHEN deleting THEN ' || CHR(10) ||
             'INSERT INTO dml_log VALUES(V_SESSION,sys_context(''userenv'', ''OS_USER''),''' ||
             V_TABNAME(X) ||
             ''',''DELETE'',sys_context(''USERENV'',''HOST''),sys_context(''USERENV'',''IP_ADDRESS''),V_PROGRAM,SYSDATE );' ||
             CHR(10) || ' END CASE;' || CHR(10) || 'END;';
    execute immediate v_sql;
  end loop;

end;

其中SPLITSTR函数见本博客:http://blog.youkuaiyun.com/edcvf3/article/details/8050978中的（二、通用的分隔函数(VARCHAR2版本)）

存储没做任何异常处理。（本人参考ITPUB上的NEWKID大大的说法,觉得异常还是留给应用处理好,所以就没做异常处理.）

四、执行存储自动给指定表增加触发器

--开始批量增加触发器
begin
  autocreate_trigger('EMP,DEPT,EMP_BAK');
end;

五、查询是否增加触发器成功并是否有效

16:09:08 SCOTT@orcl> select A.NAME, A.TYPE, B.CREATED, B.LAST_DDL_TIME, B.status
16:09:18   2    from user_dependencies a, USER_OBJECTS B
16:09:18   3   where A.type = 'TRIGGER'
16:09:18   4     AND A.REFERENCED_TYPE = 'TABLE'
16:09:18   5     AND A.REFERENCED_NAME IN ('EMP', 'DEPT', 'EMP_BAK')
16:09:18   6     AND A.name = B.OBJECT_NAME;

NAME            TYPE            CREATED        LAST_DDL_TIME  STATUS
--------------- --------------- -------------- -------------- -------
TRI_EMP         TRIGGER         08-4月 -13     08-4月 -13     VALID
TRI_DEPT        TRIGGER         08-4月 -13     08-4月 -13     VALID
TRI_EMP_BAK     TRIGGER         08-4月 -13     08-4月 -13     VALID

已选择3行。

六、验证触发器的功能

17:17:37 SCOTT@orcl> insert into emp_bak VALUES(1231,'cryking','MANGER',1,SYSDATE,12000,'',1);

已创建 1 行。

已用时间:  00: 00: 00.00

--查询日志记录表
17:15:19 SCOTT@orcl> select * from dml_log;

SESSION_ID OPER_USER       TABNAME    OPER_TYPE  SERVER_HOST          IP              RUN_PROGRAM   OPER_DATE
---------- --------------- ---------- ---------- -------------------- --------------- ------------- ---------
   2116650 WINXP-904_CRY\A EMP_BAK    INSERT     WORKGROUP\WINXP-904_ 192.168.0.229   plsqldev.exe  08-4月 13        
           dministrator                          CRY
           

-----------

最后想抓DML的语句发现实现起来比较复杂,需要相关V$SQLAREA的视图的权限,所以后来还是以审计的方式进行跟踪了。。。

仅当练习把,没有太多实际用处。