如何通过触发器将当前操作表的表名、主键字段、操作类型、操作时间保存到另外一张表
如何通过触发器将当前操作表的表名、主键字段、操作类型、操作时间保存到另外一张表

[解决办法]
update,delete,insert应该都行，SELECT的话好像只有profile了吧。。。

[解决办法]
通过触发器监控表的操作

SQL code

例一：先建立测试表.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_tgtestAFTER UPDATE or delete or insert ON TGTEST REFERENCING NEW AS New OLD AS OldFOR EACH ROWDECLARE v_type VARCHAR2(500);old_value int;new_value int;BEGINv_type:='';old_value:=:old.usercode; new_value:=:new.usercode;if(old_value='0000000000' or new_value='0000000000') then IF inserting THENv_type:='INSERT';ELSIF updating THENv_type:='UPDATE';ELSIF deleting THENv_type:='DELETE';END IF;INSERT INTO tglogselect username,sysdate,v_type,SYS_CONTEXT('USERENV','IP_ADDRESS'),machine,programfrom 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.exeFUDB 2010-07-22 17:38:34 INSERT 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exeFUDB 2010-07-22 17:38:59 INSERT 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exeFUDB 2010-07-22 17:39:34 INSERT 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exeFUDB 2010-07-22 17:41:03 UPDATE 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exeFUDB 2010-07-22 21:15:20 DELETE 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exeFUDB 2010-07-22 21:15:56 INSERT 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exeFUDB 2010-07-22 21:16:40 DELETE 192.168.3.188 MSHOME\TPADGERRARD plsqldev.exeFUDB 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_userinfoAFTER DELETE OR INSERT OR UPDATEON vhl_portal.portal_group_userREFERENCING NEW AS NEW OLD AS OLDFOR EACH ROWDECLAREv_type VARCHAR2 (50);--old_value VARCHAR2(30);--new_value VARCHAR2(30);BEGINv_type:='';--old_value:=:old.C_MEMEBER_ID;--new_value:=:new.C_MEMEBER_ID;IF INSERTINGTHENv_type:= 'INSERT';ELSIF UPDATINGTHENv_type:= 'UPDATE';ELSIF DELETINGTHENv_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;