oracle trigger 表名 列名

select 'create or replace trigger ' || t.TABLE_NAME || '_' || t.COLUMN_NAME ||
'_Update_Insert' || ' after insert or update of' || ' ' ||
t.COLUMN_NAME || ' on ' || t.TABLE_NAME || ' for each row declare
-- local variables here
begin
if updating then
' || 'insert into historydata (id,RECORDID,TABLENAME,COLNAME,' ||
decode(t.DATA_TYPE,
'VARCHAR2',
'NEWVALUECHAR',
'NUMBER',
'NEWVALUENUMBER',
'TIMESTAMP(6)',
'NEWVALUEDATE',
'FLOAT',
'NEWVALUEFLT') || ' , ' ||
decode(t.DATA_TYPE,
'VARCHAR2',
'OLDVALUECHAR',
'NUMBER',
'OLDVALUENUMBER',
'TIMESTAMP(6)',
'OLDVALUEDATE',
'FLOAT',
'OLDVALUEFLT') || ' , ' ||
'MODIFIER,MODIFYDATE,OPERATETYPE) values (''2w3e4r5t6y7u8i'',:new.id, ''' ||
t.TABLE_NAME || ''' , ''' || t.COLUMN_NAME || ''' , ' || ':new.' ||
t.COLUMN_NAME || ' , ' || ':old.' || t.COLUMN_NAME || ' , ' ||
':new.editor,sysdate,''updating'');' || '
end if; ' || ' if inserting then
' || 'insert into historydata (id,RECORDID,TABLENAME,COLNAME,' ||
decode(t.DATA_TYPE,
'VARCHAR2',
'NEWVALUECHAR',
'NUMBER',
'NEWVALUENUMBER',
'TIMESTAMP(6)',
'NEWVALUEDATE',
'FLOAT',
'NEWVALUEFLT') || ' , ' ||
decode(t.DATA_TYPE,
'VARCHAR2',
'OLDVALUECHAR',
'NUMBER',
'OLDVALUENUMBER',
'TIMESTAMP(6)',
'OLDVALUEDATE',
'FLOAT',
'OLDVALUEFLT') || ' , ' ||
'MODIFIER,MODIFYDATE,OPERATETYPE) values (''2w3e4r5t6y7u8i'',:new.id, ''' ||
t.TABLE_NAME || ''' , ''' || t.COLUMN_NAME || ''' , ' || ':new.' ||
t.COLUMN_NAME || ' , ' || ':old.' || t.COLUMN_NAME || ' , ' ||
':new.editor,sysdate,''inserting'');' || '
end if; ' || 'end ' || t.TABLE_NAME || '_' || t.COLUMN_NAME ||
'_Update_Insert ;'
from user_tab_columns t
where t.TABLE_NAME in ('AIRPORT')
and t.COLUMN_NAME != 'ID'


prompt PL/SQL Developer import file
prompt Created on 2009年6月4日 by fox
set feedback off
set define off
prompt Creating HISTORYDATA...
create table HISTORYDATA
(
ID VARCHAR2(40),
RECORDID VARCHAR2(40),
TABLENAME VARCHAR2(100),
COLNAME VARCHAR2(100),
NEWVALUECHAR VARCHAR2(200),
OLDVALUECHAR VARCHAR2(200),
NEWVALUENUMBER NUMBER(10),
OLDVALUENUMBER NUMBER(10),
NEWVALUEDATE TIMESTAMP(6),
OLDVALUEDATE TIMESTAMP(6),
VALUETYPE NUMBER,
OPERATETYPE NUMBER,
MODIFIER VARCHAR2(100),
MODIFYDATE TIMESTAMP(6),
NEWVALUEFLT FLOAT,
OLDVALUEFLT FLOAT
)
;
comment on column HISTORYDATA.VALUETYPE
is '1-int,2-str,3-date.4-float';
comment on column HISTORYDATA.OPERATETYPE
is '1-update,2-insert,3-delete';

prompt Loading HISTORYDATA...
prompt Table is empty
set feedback on
set define on
prompt Done.