Hi,
I want to log them when any changes are made to the records in an existing table.
First I tried to do it with trigger and I tried to do it separately for all columns.it is Ok.
Then I wonder if I can do them in a dynamic way I tried.
I would like your help on this.
How can I log all of the changing columns and values in the table?
If I'm trying to do this for the time being, I'm thinking of doing the insert and delete if I'm successful.
CREATE TABLE HKSB
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
city varchar2(50),
code varchar2(20),
Approve_code varchar2(10)
);
CREATE TABLE HKSB_CHANGED_LOG
( USERNAME varchar2(50),
CHANGED_DATE DATE,
ACTION varchar2(50),
ChangedColumName varchar2(50),
VALOLD varchar2(50),
VALNEW varchar2(50),
ip varchar2(50),
session_id varchar2(50),
HOST varchar2(50)
);
INSERT INTO "HKSB"("CUSTOMER_ID", "CUSTOMER_NAME", "CITY", "CODE", "APPROVE_CODE") VALUES ('1', 'asd', 'ALASKA', '01.01.020', '78996');
INSERT INTO "HKSB"("CUSTOMER_ID", "CUSTOMER_NAME", "CITY", "CODE", "APPROVE_CODE") VALUES ('2', 'qwerty', 'ALABAMA', '01.01.58.98', '96328');
INSERT INTO "HKSB"("CUSTOMER_ID", "CUSTOMER_NAME", "CITY", "CODE", "APPROVE_CODE") VALUES ('3', 'rtyud', 'CALIFORNIA', '02.02.789', NULL);
create or replace TRIGGER TEST_KASAD
AFTER UPDATE ON HKSB
FOR EACH ROW
DECLARE
updatedcol varchar2(3000);
oldval varchar(2000);
newval varchar(2000);
begin
for r in (select column_name from user_tab_columns where table_name ='HKSB')
loop
IF UPDATING(r.column_name) THEN
updatedcol := r.column_name;
execute immediate 'select :old.'||row.column_name||' from dual' into oldval;
execute immediate 'select :new.'||row.column_name||' from dual' into newval;
END IF;
end loop;
INSERT INTO HKSB_CHANGED_LOG (USERNAME,CHANGED_DATE, ACTION,ChangedColumName,VALOLD,VALNEW,ip,session_id,HOST)VALUES (USER,SYSDATE, 'Update',updatedcol,:oldval,:newval,sys_context('USERENV','IP_ADDRESS'),sys_context('USERENV','SESSIONID'),sys_context('USERENV','HOST'));
end;
I tried to do as above but I was not successful.