下面介绍通用的数据变更日志记录表及触发器的自动生成脚本,大家可以根据自己实际的需要来定义。
declare v_sqlstr varchar2(4000); begin --创建slog日志表及触发器 for i in (select tname, tcomments from ad_tab where is_slog = 1) loop begin execute immediate 'drop table slog_' || i.tname || ' purge'; exception when others then null; end; execute immediate 'create table slog_' || i.tname || ' as select 1 log_id,sysdate logdt,'' '' action,lpad('' '',100) user_ip,lpad('' '',100) user_host, a.* from ' || i.tname || ' a'; execute immediate 'comment on table slog_' || i.tname || ' is ''' || i.tcomments || '_LOG'''; --建触发器 v_sqlstr := 'create or replace trigger trg_slog_' || substr(i.tname, 1, 20) || ' after insert or update or delete on ' || i.tname || ' for each row /************************************ Created by xsb on 2006-02-05 数据变更日志记录 *************************************/ declare v_action char(1); begin if inserting then v_action := ''i''; elsif updating then v_action := ''u''; else v_action := ''d''; end if; --插入或更新记录时 if inserting or updating then insert into slog_' || i.tname || ' (log_id, logdt, action, user_host, user_ip'; for j in (select column_name from user_tab_cols where table_name = i.tname order by column_id) loop --表的列名 v_sqlstr := v_sqlstr || ', ' || j.column_name; end loop; v_sqlstr := v_sqlstr || ') values (seq_sys_log.nextval, sysdate, v_action, sys_context(''userenv'',''host''), sys_context(''userenv'',''ip_address'')'; for j in (select column_name from user_tab_cols where table_name = i.tname order by column_id) loop --表的列名 v_sqlstr := v_sqlstr || ', :new.' || j.column_name; end loop; v_sqlstr := v_sqlstr || '); end if; --删除记录时 if deleting then insert into slog_' || i.tname || ' (log_id, logdt, action, user_host, user_ip'; for j in (select column_name from user_tab_cols where table_name = i.tname order by column_id) loop --表的列名 v_sqlstr := v_sqlstr || ', ' || j.column_name; end loop; v_sqlstr := v_sqlstr || ') values (seq_sys_log.nextval, sysdate, v_action, sys_context(''userenv'',''host''), sys_context(''userenv'',''ip_address'')'; for j in (select column_name from user_tab_cols where table_name = i.tname order by column_id) loop --表的列名 v_sqlstr := v_sqlstr || ', :old.' || j.column_name; end loop; v_sqlstr := v_sqlstr || '); end if; exception when others then raise_application_error(-20001, '' 触发器执行失败 .. . '' || sqlerrm); end;'; execute immediate v_sqlstr; end loop; end; / |