200分求助：使用trigger实现备份

Question

只针对此表:create or replace trigger bak_trigger
before update or delete on emp
for each row
begin
if updating then
insert into bk_tab(OPERATION_TYPE,TABLE_NAME,OLD_VALUE,NEW_VALUE,OPERATE_TIME,OPERATER)
values('update','emp',:old.EMPNO||','||:old.ENAME||','||:old.JOB||','||:old.MGR,
       :new.EMPNO||','||:new.ENAME||','||:new.JOB||','||:new.MGR,sysdate,user);
end if;if deleting then
insert into bk_tab(OPERATION_TYPE,TABLE_NAME,OLD_VALUE,OPERATE_TIME,OPERATER)
values('update','emp',:old.EMPNO||','||:old.ENAME||','||:old.JOB||','||:old.MGR,
       sysdate,user);
end if;end;
/

Answer 1

create or replace trigger TRIGGER_TEST
  after delete or update on emp  
  for each row
declare
begin
  if deleting then 
     insert into bk_tab (OPERATION_TYPE,TABLE_NAME ,OLD_VALUE ,NEW_VALUE ,OPERATE_TIME ,OPERATER ) 
       values ('delete','emp', 
               'EMPNO:'||:old.EMPNO||',ENAME:'||:old.ENAME||',JOB:'||:old.JOB||',MGR:'||:old.MGR,
               'EMPNO:  ,ENAME:  ,JOB:   ,MGR:   ',
               sysdate, user );
  elsif updating then 
     insert into bk_tab (OPERATION_TYPE,TABLE_NAME ,OLD_VALUE ,NEW_VALUE ,OPERATE_TIME ,OPERATER ) 
       values ('update','emp', 
               'EMPNO:'||:old.EMPNO||',ENAME:'||:old.ENAME||',JOB:'||:old.JOB||',MGR:'||:old.MGR,
               'EMPNO:'||:new.EMPNO||',ENAME:'||:new.ENAME||',JOB:'||:new.JOB||',MGR:'||:new.MGR,
               sysdate, user ); 
  end if;
  
  
end TRIGGER_TEST;

Answer 2

http://community.csdn.net/Expert/topic/4971/4971956.xml?temp=.7997553

Answer 3

create or replace trigger bak_trigger
before update or delete on emp
for each row
begin
if updating then
insert into bk_tab(OPERATION_TYPE,TABLE_NAME,OLD_VALUE,NEW_VALUE,OPERATE_TIME,OPERATER)
values('update','emp','EMPNO:'||:old.EMPNO||','||'ENAME:'||:old.ENAME||','||'JOB:'||:old.JOB||','||'MGR:'||:old.MGR,
'EMPNO:'||:new.EMPNO||','||'ENAME:'||:new.ENAME||','||'JOB:'||:new.JOB||','||'MGR:'||:new.MGR,sysdate,user);
end if;if deleting then
insert into bk_tab(OPERATION_TYPE,TABLE_NAME,OLD_VALUE,NEW_VALUE,OPERATE_TIME,OPERATER)
values('delete','emp','EMPNO:'||:old.EMPNO||','||'ENAME:'||:old.ENAME||','||'JOB:'||:old.JOB||','||'MGR:'||:old.MGR,
'EMPNO:'||:new.EMPNO||','||'ENAME:'||:new.ENAME||','||'JOB:'||:new.JOB||','||'MGR:'||:new.MGR,sysdate,user);
end if;end;
/

Answer 4

只针对此表:create or replace trigger bak_trigger
before update or delete on emp
for each row
begin
if updating then
insert into bk_tab(OPERATION_TYPE,TABLE_NAME,OLD_VALUE,NEW_VALUE,OPERATE_TIME,OPERATER)
values('update','emp',:old.EMPNO||','||:old.ENAME||','||:old.JOB||','||:old.MGR,
       :new.EMPNO||','||:new.ENAME||','||:new.JOB||','||:new.MGR,sysdate,user);
end if;if deleting then
insert into bk_tab(OPERATION_TYPE,TABLE_NAME,OLD_VALUE,OPERATE_TIME,OPERATER)
values('delete','emp',:old.EMPNO||','||:old.ENAME||','||:old.JOB||','||:old.MGR,
       sysdate,user);
end if;end;
/

Answer 5

忘记说明了
关于OLD_VALUE和NEW_VALUE:
OLD_VALUE：emp表updata/delete前更新行各个字段的值，记录格式为：EMPNO:111,ENAME:aaa,JOB:bbb,MGR:222
NEW_VALUE：emp表updata/delete后更新行各个字段的值，记录格式为：EMPNO:000,ENAME:aaa,JOB:bbb,MGR:222各个字段不希望写死，因为表结构要使变化了，还要重新写trigger，希望通过查询数据库表结构的结果，将表的字段和值记录下来实在抱歉谢谢

Answer 6

表结构变化了,不仅仅是OLD_VALUE和NEW_VALUE字段内容的问题；
更新的时候使用的 :old.empno这些字段也是需要重写的，如果在触发器里查询表结构，通过循环拼出插入的OLD_VALUE和NEW_VALUE字段和其他字段等效率是很不好的；

Answer 7

那有没有办法解决当表结构变化了，而不需要重新写trigger，且可以提高效率？谢谢

Answer 8

各个字段不希望写死，因为表结构要使变化了，还要重新写trigger，希望通过查询数据库表结构的结果，将表的字段和值记录下来这个要求实现起来很难么？

Answer 9

create or replace trigger bak_trigger
  before update or delete on a111
  for each row
declare 
 v_table_name varchar2(50);
 v_text varchar2(4000);
 v_text_old  varchar2(4000);
 v_text_new  varchar2(4000);
 v_sql_old   varchar2(4000);
 v_sql_new   varchar2(4000);
begin
  v_table_name := a111;
  for c_col in (select t.COLUMN_NAME, t.DATA_TYPE
                  from user_tab_columns t
                 where t.table_name = v_table_name) loop
      v_sql_old := 'select old.'||c_col.column_name||' from dual';
      
      execute immediate v_sql_old returning into v_text;
      v_text_old:=v_text_old||c_col.column_name||':'||v_text||',';      
      v_sql_new := 'select new.'||c_col.column_name||' from dual';
      execute immediate v_sql_new returning into v_text;
      v_sql_new:=v_sql_new||c_col.column_name||':'||v_text||',';
  end loop;  if updating then
    insert into bk_tab
      (OPERATION_TYPE,
       TABLE_NAME,
       OLD_VALUE,
       NEW_VALUE,
       OPERATE_TIME,
       OPERATER)
    values
      ('update',
       v_table_name,
       v_text_old,
       v_sql_new,
       sysdate,
       user);
  end if;  if deleting then
    insert into bk_tab
      (OPERATION_TYPE,
       TABLE_NAME,
       OLD_VALUE,
       NEW_VALUE,
       OPERATE_TIME,
       OPERATER)
    values
      ('delete',
       v_table_name,
       v_text_old,
       v_sql_new,
       sysdate,
       user);
  end if;end;

Answer 10

to guangli_zhang(广丽) :按照你的方法，可是出现错误：delete emp_a where empno= '7934'
       *
ERROR 位于第 1 行:
ORA-00904: "OLD"."EMPNO": 无效的标识符
ORA-06512: 在"SCOTT.BAK_TRIGGER", line 16
ORA-04088: 触发器 'SCOTT.BAK_TRIGGER' 执行过程中出错请问如何解决，谢谢

Answer 11

to 楼主：
1、建bk_tab表的目的是什么？只是为了记录什么时间执行了那些操作吗？还是有其他的考虑
2、bk_tab表结构设计是否合理？
因为现有表emp结构简单，并不具有普遍性，如果表的结构复杂，例如：列类型的改变（blob,clob等等）、列的增加等等），这些都可能导致bk_tab表现有列无法存储。
3、是否考虑过其他的实现方式（如添加标识列、表复制、日志等等），为什么最后选择这样做

Answer 12

to waterfirer(水清)：1、主要是为了记录这些操作，同时，当数据出现问题时，方便查询数据的原值和修改后的值，以便知道数据修改的履历2、这个表结构应该没有问题，备份的表不会包含blob等不能存储的列。我这里也想到了表结构发生变化的时候，不想再修改trigger，所以提出了“希望通过查询数据库表结构的结果，将表的字段和值记录下来”，不知道水清还有什么更好的建议3、不知道水清老大还有什么更好的办法么？

Answer 13

希望通过查询数据库表结构的结果，将表的字段和值记录下来
----------
select column_name from user_tab_columns where table_name='yourtable'
可以在trigger里写
不知道水清老大还有什么更好的办法么？
----------
方法有很多，关键是要找一个合适的方法。

Answer 14

select column_name from user_tab_columns where table_name='yourtable'
可以在trigger里写
==============================
但是怎么拼成可以记录原值和新值得sql？方法有很多，关键是要找一个合适的方法。
==============================
根据我的需求，您认为那个方法最合适。谢谢