oracle 触发器中能否动态获取某个字段的new 和 old值？

我现在做的一个系统分为内外网，要必须物理隔离，所以必须进行数据交互。因此，内外网数据库结构相同，都有可能进行数据修改操作。此时如果进行内外网的数据交互时，就要考虑同步的问题。我设计了一个表sys_RecordChangeLog，专门用来记录所有表的记录修改过程，给每个表建三个触发器：INSERT UPDATE DELETE，然后生成操作的SQL语句，保存在sys_RecordChangeLog中。

但我不想为每个表都写一遍触发器代码，而是想通过一个通用的函数实现。以下是我的想法：

create or replace trigger trg_update_Table1
  before Update on table1
  for each row

declare
  sSQL varchar2(30000);

begin
  :new.Version:=nvl(:old.Version,0)+1;

  sSQL :='Update table1 set ';

  for rdField in (select column_Name from user_tab_cols where tablename='Table1') loop
    if :old.(字段名rdField.column_name)<>:new.(字段名rdField.column_name) then
      sSQL :=sSQL ||'  '||rdField.column_name||'='||:new.(字段名rdField.column_name)||',';
    end if;
  end loop;

  sSQL:=sSQL||' where ID='||:old.ID;

end trg_update_Table1;

Oracle中有这样的用法吗？或者给一个更好的解决方案！谢谢！

15 个解决方案

#1

建议外网数据库个表增加一个字段flag,标记0,1.
0代表改条数据没有更新过,1代表更新过.

数据交换时,只把标记为1的数据更新到内网的库的各表,交换完毕后,把标记更新为0.

#2

弱弱的问一句，如果内外网同时修改了数据，以谁的数据为准

#3

1.动态获取new或old的值,觉得不明白这个意思?估计你这样做如果出错了,检查起来就麻烦了

2.每个表建三个触发器,如果插入或修改的数据多的时候,会很慢的

#4

动态获取new或old的值，什么情况下是new的值，什么时候才算是old呢？？？
这个我一直很郁闷。。。
还是不清楚！！

#5

楼主我理解你的意思，不知道你怎么解决这个问题的？

#6

引用楼主 bmwhans 的回复:

我现在做的一个系统分为内外网，要必须物理隔离，所以必须进行数据交互。因此，内外网数据库结构相同，都有可能进行数据修改操作。此时如果进行内外网的数据交互时，就要考虑同步的问题。我设计了一个表sys_RecordChangeLog，专门用来记录所有表的记录修改过程，给每个表建三个触发器：INSERT UPDATE DELETE，然后生成操作的SQL语句，保存在sys_RecordChangeLog中。

但我不想为每个表都写一遍触发器代码，而是想通过一个通用的函数实现。以下是我的想法：

create or replace trigger trg_update_Table1
   before Update on table1
   for each row

declare
   sSQL varchar2(30000);

begin
   :new.Version:=nvl(:old.Version,0)+1;

   sSQL :='Update table1 set ';

   for rdField in (select column_Name from user_tab_cols where tablename='Table1') loop
     if :old.(字段名rdField.column_name) <>:new.(字段名rdField.column_name) then
       sSQL :=sSQL ||'  '||rdField.column_name||'='||:new.(字段名rdField.column_name)||',';
     end if;
   end loop;

   sSQL:=sSQL||' where ID='||:old.ID;

end trg_update_Table1;

Oracle中有这样的用法吗？或者给一个更好的解决方案！谢谢！

想法是好的，不过好像Oracle无法用这样的方式实现。

#7

通用的函数?
呵呵楼主的意思我了解
可是如果用函数来实现我就不知道该怎么办了

#8

好像 :old ,:new 处理Null值，有点问题
实现起来比较复杂

#9

这种处理,不建议放在SQL中来执行,直接放在程序端执行好!

#10

楼上各位大哥,3月份的帖呢!!
):

#11

不知道楼主后来有没有顺利的解决这个问题

不过两边同时会有修改,真的很纠结啊

#12

我关注:old.字段名,字段名是一个变量，请问这样可以应用不？
create or replace trigger trigger_CLXX
  before INSERT OR UPDATE OR DELETE on wsba.hx_a_clxx
for each row
-- local variables here

DECLARE
CURCHGCOL VARCHAR2(30);
  CHGCOL VARCHAR2(1000);
  CHGCOLVALUE CLOB;
  TEMP1 VARCHAR(30);
  TEMP2 VARCHAR(30);
  EQUALS varchar(1);
  STRSQL varchar(300);
begin
-- query all colnames
-- get changed colnames and values
FOR j IN (select COLUMN_NAME from user_tab_columns where upper(Table_name) = upper('HX_A_CLXX') AND DATA_TYPE NOT IN('CBLOB','BLOB','NCLOB','BFILE')) Loop
CURCHGCOL:=j.COLUMN_NAME;
TEMP1:=':old.'||CURCHGCOL;
TEMP2:=':new.'||CURCHGCOL;
-- TEMP1:=CURCHGCOL;
-- TEMP2:=CURCHGCOL;
STRSQL:='SELECT case when '||TEMP1||'='||TEMP2||' then "1" else "0" end from dual ';
execute immediate STRSQL into EQUALS;
IF(EQUALS<>'1') THEN
CHGCOL:=CONCAT(CHGCOL,CONCAT(CURCHGCOL,','));
CHGCOLVALUE:=CONCAT(CHGCOLVALUE,CONCAT(TEMP2,','));
END IF;
END LOOP;
CHGCOL:=SUBSTR(CHGCOL,1,LENGTH(CHGCOL)-1);
CHGCOLVALUE:=SUBSTR(CHGCOLVALUE,1,LENGTH(CHGCOLVALUE)-1);
IF INSERTING THEN
-- when insert into table
insert into GX_LS_LSXX(guid,MSM,BM,BL,BLZ,LRRYBH,LRRYMC,LRRYJH,LRRYBMBH,LRRYBMMC,LRSJ)
values (sys_guid(),'WSBA','HX_A_CLXX',CHGCOL,CHGCOLVALUE,:new.LRRYBH,:new.LRRYMC,:new.LRRYJH,:new.LRRYBMBH,:new.LRRYBMMC,:new.LRSJ);
ELSIF UPDATING THEN
-- when update table values
insert into GX_LS_LSXX(guid,MSM,BM,BL,BLZ,LRRYBH,LRRYMC,LRRYJH,LRRYBMBH,LRRYBMMC,LRSJ)
values (sys_guid(),'WSBA','HX_A_CLXX',CHGCOL,CHGCOLVALUE,:new.LRRYBH,:new.LRRYMC,:new.LRRYJH,:new.LRRYBMBH,:new.LRRYBMMC,:new.LRSJ);
ELSE
insert into GX_LS_LSXX(guid,MSM,BM,BL,BLZ,LRRYBH,LRRYMC,LRRYJH,LRRYBMBH,LRRYBMMC,LRSJ)
values (sys_guid(),'WSBA','HX_A_CLXX',CHGCOL,CHGCOLVALUE,:old.LRRYBH,:old.LRRYMC,:old.LRRYJH,:old.LRRYBMBH,:old.LRRYBMMC,:old.LRSJ);
END IF;
end trigger_CLXX;
上面的做法可以成功创建触发器，但是总是在触发时提示变量不能绑定（在oracle10g测试）

#13

我也執行了半天，會報錯ORA-01008: 部份變數未被連結。
v_sql = 'select fun_str_replace(:new.id) from dual';
execute IMMEDIATE v_sql INTO INTO v_id ;

#14

最後你們是怎麼解決的？

#15

when updating(字段名) 可以试一下这个我用过可以

#1

#2