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