给指定的一批表自动加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 C