日期:2014-05-17 浏览次数:20544 次
例一: 先建立测试表. create table TGLOG ( USERCODE VARCHAR2(25) not null, TIME DATE not null, TYPE VARCHAR2(25) not null, IP VARCHAR2(25) not null, RAMARK1 VARCHAR2(25) not null, REMARK2 VARCHAR2(225) ); create table TGTEST ( USERCODE VARCHAR2(10) not null, LMAGENTID VARCHAR2(25) not null, REMARK VARCHAR2(225), VALIDSTATUS VARCHAR2(1) not null, FLAG VARCHAR2(1) ); 目标:对TGTEST的inser,update,delete 都记录下相应信息 (操作类型,username,timestamp,ip,machine,module),并且只跟踪usercode='0000000000'的数据. CREATE OR REPLACE TRIGGER tg_trace_tab_tgtest AFTER UPDATE or delete or insert ON TGTEST REFERENCING NEW AS New OLD AS Old FOR EACH ROW DECLARE v_type VARCHAR2(500); old_value int; new_value int; BEGIN v_type:=''; old_value:=:old.usercode; new_value:=:new.usercode; if(old_value='0000000000' or new_value='0000000000') then IF inserting THEN v_type:='INSERT'; ELSIF updating THEN v_type:='UPDATE'; ELSIF deleting THEN v_type:='DELETE'; END IF; INSERT INTO tglog select username,sysdate,v_type,SYS_CONTEXT('USERENV','IP_ADDRESS'),machine,program from v$session where AUDSID = USERENV('SESSIONID'); end if; END; / OK,可以开始测试了. INSERT INTO tgtest SELECT '0000000000','22','333','1','1' FROM dual ; COMMIT ; UPDATE tgtest SET flag=0 WHERE usercode='0000000000'; COMMIT ; DELETE FROM tgtest WHERE usercode='0000000000'; COMMIT; SELECT * FROM tglog ORDER BY TIME ASC ; USERCODE TIME TYPE IP RAMARK1 REMARK2 ---------- ------------------- ---------- ------------------------- ------------------------- -------------------- FUDB 2010-07-22 17:20:20 111 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exe FUDB 2010-07-22 17:38:34 INSERT 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exe FUDB 2010-07-22 17:38:59 INSERT 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exe FUDB 2010-07-22 17:39:34 INSERT 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exe FUDB 2010-07-22 17:41:03 UPDATE 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exe FUDB 2010-07-22 21:15:20 DELETE 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exe FUDB 2010-07-22 21:15:56 INSERT 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exe FUDB 2010-07-22 21:16:40 DELETE 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exe FUDB 2010-07-22 21:32:20 INSERT 192.168.3.49 WORKGROUP\MESORACLE sqlplus.exe 例二: 被监控表: create table vhl_portal.PORTAL_GROUP_USER ( C_GROUP_ID VARCHAR2(255) not null, C_MEMEBER_ID VARCHAR2(255) not null, C_MEMEBER_TYPE VARCHAR2(1) ) 记录日志表: create table vhl_portal.T_LOG_CHANGE_USERINFO ( C_OPER_USER VARCHAR2(30) not null, T_CHG_TM DATE not null, C_CHG_TYPE VARCHAR2(25) not null, IP VARCHAR2(30) not null, MACHINE VARCHAR2(50) not null, PROGRAM VARCHAR2(300) not null, REMARK1 VARCHAR2(300), REMARK2 VARCHAR2(300), REMARK3 VARCHAR2(300) ) 触发器,需要有触发器中查看v$session权限的用户中创建,这里我们用SYS: CREATE OR REPLACE TRIGGER tgr_log_change_userinfo AFTER DELETE OR INSERT OR UPDATE ON vhl_portal.portal_group_user REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE v_type VARCHAR2 (50); --old_value VARCHAR2(30); --new_value VARCHAR2(30); BEGIN v_type:=''; --old_value:=:old.C_MEMEBER_ID; --new_value:=:new.C_MEMEBER_ID; IF INSERTING THEN v_type:= 'INSERT'; ELSIF UPDATING THEN v_type:= 'UPDATE'; ELSIF DELETING THEN v_type:= 'DELETE'; END IF; INSERT INTO vhl_portal.t_log_change_userinfo SELECT username,SYSDATE,v_type,SYS_CONTEXT('USERENV','IP_ADDRESS'), machine, program,null,null,null FROM v$session WHERE audsid = USERENV('SESSIONID'); END;