Dynamic SQL assignment

676674

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

Rob van Wijk

Alex,

If you are using version 11g, then you can use flashback data archives for these journaling purposes. You can read about that here on my blog: http://rwijk.blogspot.com/2008/11/journaling-using-flashback-data.html

If you are using an older version, you need to code it by hand and you'll want to use the builtin booleans updating, inserting and deleting. These tell you exactly what the operation was without having to check if any of the columns have changed. You can read about them here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#i1025109

Regards,
Rob.

Edited by: Rob van Wijk on 21-dec-2008 17:29

Added link

Etbin

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055 is also worth to take a look at

Regards

Etbin

Gurjas

You can use FGA (Fine Grain Auditing) to get all the queries run on the particular table.