need help about writing an oracle trigger

[new_drummerboy]

Hi

I'm just a new one in oracle.
I have to create a table which contain history of a main table. like this:

if the main table is
========================
name type length not null
A varchar 5 Y
B varchar 5 N
C varchar 5 N
D varchar 5 N
========================

its has 1 record inside :
Record A B C D
1 "aaa" "BBB" "CCC" "DDD"

and then I runing this SQL statement :
update main_table set B="DEF" and C="XYZ" where A="aaa"

the main_table will become
Record A B C D
1 "aaa" "DEF" "XYZ" "DDD"

and the history table must contain 2 record
Record Coll OLD NEW
1 "B" "BBB" "DEF"
1 "C" "BBB" "XYZ"

I've plan to so this by create a trigger in main_table. my problem is my main table have a lot of fields and I can't write a code to control it 1 by 1 like :
if old.A <> new.a
insert into history("A",old.A,new.a)
if old.B <> new.B
insert into history("B",old.b,new.b)
......

I decided to select column name from the data dictonary using this SQL:
SELECT column_name FROM user_tab_columns WHERE table_name = '<<Table Name>>';
and then do a loop over the resultset and use the column name I've got , like this (its just an idea, may be not a write syntax):

BEGIN
.....
FOR i IN 1..:result.COUNT LOOP
if ld.colname[i] <> :new.colname[i]
INSERT INTO history
VALUES ( colname[i], ld.colname[i], :new.colname[i]);
END LOOP;
END;

but I can't write a "old.colname". I try with " old.'colname' ", " ld.'colname' " but it won't work.
if anyone know how to create a history file like I've describe, please help me.

thanks in advance
Drummerboy

[ghuener]

In order to create a dynamic sql statement, you'll need to use the DBMS_SQL package supplied by Oracle. With that package, you'll be able to construct a string with your column names and values in it to apply to the database. check metalink and/or otn to get docs and examples of this method.
Alternatively, you can use Oracle's built-in audit trail to do what your are doing via triggers.
Good luck.

[new_drummerboy]

I try to look at DBMS_SQL. but for now I can't see how this could solve my issue.

with DBMS_SQL, I see how to create a dynamic SQL like select :X from a where KEY='AA' or something like that. but my problem is not about create a dynamic SQL, but its about refering to data in OLD and NEW in before update trigger in a DYNAMIC way, ie. instead of get OLD.SALARY, I may set parameter X = "SALARY" and then get the OLD.X or something like that.

I don't know this is possible or not. I'm not familiar with Oracle, so please explain more.

thanks alot

[ghuener]

you can use dbms_sql to construct the entire sql statement and use it in a cursor: the level of difficulty to implement decreases the more you know about the format of your statement (number of columns and data types, specifically)

fsSql := 'SELECT '||fsColumn||' FROM '||fsTable||' WHERE '||fsWhere;

...

there are many examples online using this method.

[stevebucknor1]

In order to create a dynamic sql statement, you'll need to use the DBMS_SQL package supplied by Oracle. With that package, you'll be able to construct a string with your column names and values in it to apply to the database. check metalink and/or otn to get docs and examples of this method. This is really amazing.