|
本文主要介绍了通用的数据变更日志记录表及触发器的自动生成脚本，大家可以根据自己实际的需要来自行定义。
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;
/ | |
