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.