Oracle :new 与:old 和变量的问题

Question

我在表MEM_MEMBER中建立一个触发器，因为表MEM_MEMBER 中字段太多，我要循环表MEM_MEMBER中的字段来比较new 和old值 我要循环表MEM_MEMBER中的字段,如:new.mem_member_id或者:old.mem_member_id我想把表MEM_MEMBER中的所有的字段用一个变量表示出来，如:new.v_column_name 我已经通过游标把字段名都取出来了，我要比较每个字段是否有变化，然后我再插入另一个表中，可是有问题，怎么办？
create or replace trigger TR_MEM_MEMBER_LOG
after INSERT OR UPDATE OR DELETE ON MEM_MEMBER FOR EACH ROW
declare
cursor CUR_MEM_MEMBER_LOG_DATA is
       select column_name,column_id from user_tab_cols where table_name='MEM_MEMBER' order by  column_id; 
       v_column_name user_tab_cols.column_name%type;
       v_column_id user_tab_cols.column_id%type;      
BEGIN 
--更新时写日志
IF UPDATING THEN
  OPEN CUR_MEM_MEMBER_LOG_DATA;
      LOOP
         FETCH  CUR_MEM_MEMBER_LOG_DATA INTO v_column_name,v_column_id;                
         EXIT WHEN CUR_MEM_MEMBER_LOG_DATA%NOTFOUND;
         --有问题
         IF ':NEW.'||v_column_name <> ':OLD.'||v_column_name THEN
            INSERT INTO MEM_MEMBER_LOG(MEMBER_LOG_ID,MEMBER_ID,MENMBER_FIELDS,MENMBER_COUNT,MEMBER_LOG_FLAG,OPERATION_DATE) 
            Values(SEQ_MEM_MEMBER_LOG.NEXTVAL,:OLD.MEMBER_ID, v_column_name,':OLD.'||v_column_name,'2',SYSDATE);
          End IF ; 
        End loop; 
   close CUR_MEM_MEMBER_LOG_DATA;
End IF;
End TR_MEM_MEMBER_LOG;

Answer 1

那个表MEM_MEMBER有70多个字段，我要写70多个if吗？

Answer 2

CREATE OR REPLACE TRIGGER TR_MEM_MEMBER_LOG
  AFTER INSERT OR UPDATE OR DELETE ON MEM_MEMBER
  FOR EACH ROW
DECLARE
  CURSOR CUR_MEM_MEMBER_LOG_DATA IS
    SELECT COLUMN_NAME, COLUMN_ID
      FROM USER_TAB_COLS
     WHERE TABLE_NAME = 'MEM_MEMBER'
     ORDER BY COLUMN_ID;
  V_COLUMN_NAME USER_TAB_COLS.COLUMN_NAME%TYPE;
  V_COLUMN_ID   USER_TAB_COLS.COLUMN_ID%TYPE;
  
  V_N_COLUMN_NAME_VALUE VARCHAR2(200);
  V_O_COLUMN_NAME_VALUE VARCHAR2(200);
BEGIN
  --更新时写日志
  IF UPDATING THEN
    OPEN CUR_MEM_MEMBER_LOG_DATA;
    LOOP
      FETCH CUR_MEM_MEMBER_LOG_DATA
        INTO V_COLUMN_NAME, V_COLUMN_ID;
      EXIT WHEN CUR_MEM_MEMBER_LOG_DATA%NOTFOUND;
      --有问题
      --改成下面这样，但是需要注意表中的数据类型，最好在这个位置增加一个数据类型的判断
      --如果是普通类型的没问题，varchar(200)可以，但是如果是日期类型的需要一个to_char函数转换
      EXECUTE IMMEDIATE 'SELECT :NEW.'|| V_COLUMN_NAME||',:OLD.'||V_COLUMN_NAME||' FROM DUAL'
                   INTO V_N_COLUMN_NAME_VALUE,V_O_COLUMN_NAME_VALUE;
      
      IF V_N_COLUMN_NAME_VALUE <> V_O_COLUMN_NAME_VALUE THEN
        INSERT INTO MEM_MEMBER_LOG
          (MEMBER_LOG_ID,
           MEMBER_ID,
           MENMBER_FIELDS,
           MENMBER_COUNT,
           MEMBER_LOG_FLAG,
           OPERATION_DATE)
        VALUES
          (SEQ_MEM_MEMBER_LOG.NEXTVAL,
           :OLD.MEMBER_ID,
           V_COLUMN_NAME,
           ':OLD.' || V_COLUMN_NAME,
           '2',
           SYSDATE);
      END IF;
    END LOOP;
    CLOSE CUR_MEM_MEMBER_LOG_DATA;
  END IF;
END TR_MEM_MEMBER_LOG;

Answer 3

MEM_MEMBER表有76个字段，我要写76个if吗？

Answer 4

EXECUTE IMMEDIATE 'SELECT :NEW.'|| V_COLUMN_NAME||',:OLD.'||V_COLUMN_NAME||' FROM DUAL' INTO V_N_COLUMN_NAME_VALUE,V_O_COLUMN_NAME_VALUE; 在编译没有问题，可是执行就问题了怎么结解

Answer 5

自己说明下，这里这么写有问题，犯了经验主义错误。--写了个脚本生成主要部分的代码，楼主还是一个一个写吧。
DECLARE
  CURSOR CUR_MEM_MEMBER_LOG_DATA IS
    SELECT COLUMN_NAME, COLUMN_ID
      FROM USER_TAB_COLS
     WHERE TABLE_NAME = 'MEM_MEMBER'
     ORDER BY COLUMN_ID;
  V_COLUMN_NAME USER_TAB_COLS.COLUMN_NAME%TYPE;
  V_COLUMN_ID   USER_TAB_COLS.COLUMN_ID%TYPE;
  V_STRSQL LONG;
BEGIN
  OPEN CUR_MEM_MEMBER_LOG_DATA;
  LOOP
    FETCH CUR_MEM_MEMBER_LOG_DATA
      INTO V_COLUMN_NAME, V_COLUMN_ID;
    EXIT WHEN CUR_MEM_MEMBER_LOG_DATA%NOTFOUND;      V_STRSQL :=V_STRSQL||'
      IF UPDATING('''||V_COLUMN_NAME||''') THEN '||
        'INSERT INTO MEM_MEMBER_LOG '||
          '(MEMBER_LOG_ID, '||
          ' MEMBER_ID, '||
          ' MENMBER_FIELDS, '||
          ' MENMBER_COUNT, '||
          ' MEMBER_LOG_FLAG, '||
          ' OPERATION_DATE) '||
       ' VALUES '||
          ' (SEQ_MEM_MEMBER_LOG.NEXTVAL, '||
          ' :OLD.MEMBER_ID, '||
          ''''|| V_COLUMN_NAME||''','||
          ' :OLD.'|| V_COLUMN_NAME||',' ||
          ' ''2'', '||
          '  SYSDATE); '||
       ' END IF;';          
  END LOOP;
  CLOSE CUR_MEM_MEMBER_LOG_DATA;
  
  --这里动态生成触发器只要部分
  EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER MY_TRIGGER '||
                    'BEFORE UPDATE ON MEM_MEMBER '||
                    'FOR EACH ROW '||
                    'BEGIN '||
                     V_STRSQL ||
                    'END;'; 
END;