SQL & PL/SQL

Background:

I am writing changes history program for some important tables I need to keep a close notice at.

One table has 42 columns (EDT), the other 30. (ADT)
Basically for each update/insert/delete that happens to any one of those tables, a row with info will be added to a table i created (ALEX_CHANGES_TBL)

I wrote triggers (3 for each table, insert/delete/update) in PL/SQL that manages this, which at the moment looks like this: (only base structure)

DECLARE
NEW as new_row OLD as old_row
BEGIN
IF new_row.COLUMN1 <> old_row.COLUMN1 THEN
INSERT INTO ALEX_CHANGES_TBL
(TABLE_NAME, FIELD_NAME, PREVIOUS_VALUE, NEW_VALUE)
VALUES('EDT', 'column1', old_row.COLUMN1, new_row.COLUMN1);
END IF;

...

IF ***.COLUMN42 <> ***.COLUMN42 THEN
...
END IF;
END;

As you understand, this is tiring, uncomfortable, and plainly stupid to write such a code.

I need to find a way to make it dynamic, initializing an array or something once with the column names and then running in some loop and checking the changes.

Simply an array is not possible since I can't address to new_row/old_row by the column name (like in .NET using [ ])

I am almost sure I can't use EXECUTE IMMEDIATE since 'new_row' and 'old_row' is not defined in the SELECT context that will be used in the dynamic SQL statement.


Just wondering on a good way to write it.


Thank you,
Alex

Edited by: user10732002 on Dec 21, 2008 2:07 AM