SQL & PL/SQL

Hi all,

I have a client/server database and to develop a system in which
updated column’s old value of any table will be insert into a common table
by post_update or on_update or any database level trigger at the time of update.
Main task is to find out the updated table and columns name.

I am giving a sample -

I have 3 tables Table1, Table2, Backup_Table with structure like as

Create table Table2(
T1C1 VARCHAR2(10),
T1C2 DATE,
T1C3 NUMBER);

Create table Table3(
T2C1 VARCHAR2(10),
T2C2 DATE,
T2C3 NUMBER,
T2C4 VARCHAR2(25));

Create table Backup_Table(
Table_Name VARCHAR2(6),
Column_Name VARCHAR2(4),
Old_Value VARCHAR2(25),
Updated_Date Date default sysdate,
Upd_User VARCHAR2(10) default user);

When I will update a column or all columns of Table1 or Table2 of a record via Screen or SQL*Plus then
the old values of columns of that updated record will be insert into Backup_Table like as

Table_Name Column_Name Old_Value Updated_Date Upd_User
---------- ----------- --------- ------------ --------
Table1 T1C1 Mr Aman 24-Aug-04 User1
Table1 T1C2 500.50 24-Aug-04 User1
Table2 T2C2 22-Aug-04 25-Aug-04 User3
Table1 T2C3 350.00 26-Aug-04 Rira
Table2 T2C1 Mr Wahid 26-Aug-04 User3
Table1 T1C1 Mr Imam 27-Aug-04 User5
Table1 T1C2 24-Aug-04 27-Aug-04 User5
Table1 T1C3 1000.00 27-Aug-04 User5
Table2 T2C4 By Cash 28-Aug-04 User2

i.e. Updated values of all types (Varchar2, Date, Number) will store as string in same column i.e. in Old_Value of table Backup_Table.

There are no PK/FK relation betweens tables.

How it is possible?

Any suggestion will be highly appreciated.

My system is Oracle8i, Dev6i, Windows 2000.

Best regards.

Dulal