This discussion is archived
1 Reply Latest reply: Nov 11, 2012 11:50 PM by 946054 RSS

Need help with trigger.

946054 Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points