日志表及触发器脚本生成器的实例参考脚本- 大学生计算机技术网- cccbbs.net
下面介绍通用的数据变更日志记录表及触发器的自动生成脚本,大家可以根据自己实际的需要来定义。


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;
/
郑重声明:资讯 【日志表及触发器脚本生成器的实例参考脚本- 大学生计算机技术网- cccbbs.net】由 发布,版权归原作者及其所在单位,其原创性以及文中陈述文字和内容未经(企业库qiyeku.com)证实,请读者仅作参考,并请自行核实相关内容。若本文有侵犯到您的版权, 请你提供相关证明及申请并与我们联系(qiyeku # qq.com)或【在线投诉】,我们审核后将会尽快处理。
—— 相关资讯 ——