1 Reply Latest reply: Nov 12, 2012 1:50 AM by piyush9010 RSS

    Need help with trigger.

    piyush9010
      Hi,

      I want a on update trigger which will store old value of the column that has been updated.

      CREATE OR REPLACE TRIGGER ORAOWNER.U_SRT_STEPS_STEP_NUM
      BEFORE UPDATE
      OF EMPNO
      ON ORAOWNER.EMP5
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
      DECLARE
      CURSOR cur_col_name
      IS
      SELECT column_name
      FROM all_tab_cols
      WHERE table_name = 'EMP5';
      v_string_col varchar2(40);
      v_string_old varchar2(40);
      v_string_new varchar2(40);
      BEGIN
      --srt.srt_change_history_procs.insert_into_log('SRT_STEPS','SRT','M','STEP_NUM',:new.step_num,'N');
      FOR v_StatsRecord IN cur_col_name
      LOOP
      IF UPDATING (v_StatsRecord.column_name)
      THEN
      v_string_col:=v_StatsRecord.column_name;
      /*SELECT (select ':new.'||v_string_col
      from dual) INTO V_STRING_NEW FROM DUAL;*/
      EXECUTE IMMEDIATE'BEGIN V_STRING_NEW:=:NEW.'||V_STRING_COL||'; END;';
      /*SELECT (select ':old.'||v_string_col
      from dual) INTO V_STRING_OLD FROM DUAL;*/
      EXECUTE IMMEDIATE 'BEGIN V_STRING_OLD:=:OLD.'||V_STRING_COL||'; END;';
      -- dbms_output.putline(v_StatsRecord.column_name||
      -- SYSDATE||
      -- v_string_old||
      -- v_string_new);
      Insert into TEST_INSERT_MASTER
      (ISSUE_NO, I_DATE, GROWER, TOT_AMT)
      Values
      (V_STRING_COL,SYSDATE, V_STRING_OLD,V_STRING_NEW);
      END IF;
      END LOOP;
      END;
      /

      I am trying to have column name dynamically which is been updated.

      Please help.