使用触发器监视数据表修改
/*记录对表进行修改或删除的用户、主机、时间和操作命令,写入TBL_LOG_UPD_WASTE表*/ /*需要Oracle版本在8.0以上*/
CREATE TABLE ITS_ADMIN.TBL_LOG_UPD_WASTE ( db_user varchar(200) default USER, when date default SYSDATE, db_domain varchar2(200) default SYS_CONTEXT(‘USERENV‘,‘DB_DOMAIN‘), host varchar2(256) default SYS_CONTEXT(‘USERENV‘,‘HOST‘), ip_address varchar2(256) default SYS_CONTEXT(‘USERENV‘,‘IP_ADDRESS‘), language varchar2(256) default SYS_CONTEXT(‘USERENV‘,‘LANGUAGE‘), protocol varchar2(200) default SYS_CONTEXT(‘USERENV‘,‘NETWORK_PROTOCOL‘), terminal varchar2(200) default SYS_CONTEXT(‘USERENV‘,‘TERMINAL‘), sql_text clob ); /
/* Require Oracle version above 8.0*/ CREATE OR REPLACE TRIGGER ITS_ADMIN.TRG_BEF_UPD_WASTE BEFORE UPDATE OR DELETE ON TBL_WASTE_CURRENT DECLARE v_lines INTEGER; v_dml ORA_NAME_LIST_T; v_clob CLOB; v_terminal VARCHAR2(30); BEGIN v_lines := ORA_SQL_TXT (v_dml); FOR i IN 1 .. v_lines LOOP v_clob := v_clob || CHR(10) || v_dml(i); END LOOP; v_clob := LTRIM (v_clob, CHR(10)); INSERT INTO TBL_LOG_UPD_WASTE (SQL_TEXT) VALUES (v_clob); END; /
|