给指定的一批表自动加DML触发器
为了跟踪一批表的操作记录,需要对该批表做触发器进行跟踪(没有用审计,因为审计需要有SYS权限),通过一个一个表写触发器,显然不太现实,
Oracle又不支持同时对多个表建立一个触发器.这里我就用存储过程的方式实现自动创建触发器了.
注:本人还是不建议使用大量触发器的,因为大量触发器会影响性能,而且据说触发器是串行操作的,那么速度上肯定会打折扣,还有一个原因就是触发器容易造成不能预知的递归触发。
一、首先我们创建日志记录表(某位大神的话,触发器一般只用作日志记录就好)
[sql]
--创建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 '修改的日期';
二、给要加触发器的表的拥有者给创建触发器的权限
[sql]
15:06:08 SYS@orcl> GRANT CREATE TRIGGER TO SCOTT;
授权成功。
三、写自动创建的存储过程
[sql]
--创建存储自动创建对应表的触发器
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;
--自动批量创建触发器
egin
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;
nd;
其中SPLITSTR函数见本博客:http://www.zzzyk.com/database/201301/181638.html 中的（二、通用的分隔函数(VARCHAR2版本)）
存储没做任何异常处理。（本人参考ITPUB上的NEWKID大大的说法,觉得异常还是留给应用处理好,所以就没做异常处理.）
四、执行存储自动给指定表增加触发器
[sql]
--开始批量增加触发器
begin
autocreate_trigger('EMP,DEPT,EMP_BAK');
end;
五、查询是否增加触发器成功并是否有效
[sql]
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
--------------- --------------
