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

Question

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

Answer 1

update,delete,insert应该都行，SELECT的话好像只有profile了吧

Answer 2

通过触发器监控表的操作例一：
先建立测试表.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;