| Referencing the Value of a Variable whose name is Stored in a Text Field [message #39584] |
Wed, 24 July 2002 09:57  |
Doug S.
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
I want to insert a record into my logging/audit table every time any field changes in my master table. I've added a Before-Update-Trigger to the table to check for any changes between :OLD.* and :NEW.* one field at a time.
I don't want to write an IF statement for each field because I would like a more generic approach (for reusability). I declare and loop through a cursor selecting from the DBA_TAB_COLUMNS table for the particular table that I'm auditing. For each changed column, I want to INSERT a record into the other table. My problem is that after selecting the column name from the system table, I don't know how to reference that column with :OLD and :NEW. I don't want to insert ":OLD.FIELD", I want to save the value of the variable :OLD.FIELD.
I know that this is not possible in many programming languages, so my hopes are not too high on finding help. Thank you for your time pondering this problem.
|
|
|
|
| Re: Referencing the Value of a Variable whose name is Stored in a Text Field [message #39585 is a reply to message #39584] |
Wed, 24 July 2002 10:29  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
This is a common question, but you cannot dynamically refer to the :old and :new values of columns using a data dictionary query. The most generic approach is to use the data dictionary to help you write the trigger (automate this step) - which is something I use frequently. Of course, when a column is later added, you still have to manually modify the trigger.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
