hi, I have to track the data changes in a single audit table test_audit which can be used in other triggers to track the changes in another tables as well. I have tried this code but stuck in populating old and new values . Any help on this is much appreciated.
create table test (ID NUMBER , ENAME VARCHAR2(10) , LOCATION VARCHAR2(10)); create or replace trigger my_trigger after update on TEST for each row declare ACTION VARCHAR2(10); begin IF DELETING THEN ACTION := 'DELETE'; ELSIF UPDATING THEN ACTION := 'UPDATE'; END IF; for i in (select column_name,table_name from all_tab_columns where table_name = 'TEST' ) loop if updating(i.column_name) then INSERT INTO TEST_AUDIT ( TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE, ACTION, UPDATED_BY, UPDATED_DT ) VALUES ( i.table_name, i.column_name, -- :old.column_name,--- how to get old and new values? -- :new.column_name,--- ACTION, USER, SYSDATE ); end if; end loop; end;