为了跟踪一批表的操作记录,需要对该批表做触发器进行跟踪(没有用审计,因为审计需要有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.csdn.net/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的视图的权限,所以后来还是以审计的方式进行跟踪了。。。
仅当练习把,没有太多实际用处。