I am working on Oracle forms 10g (OS: Windows7) from last 1 year. I have got a requirement to show the history of table in forms. Now i need to highlight only the modified columns in a row using visual attribute. I have written a AFTER UPDATE database trigger in order to insert OLD values into a log table. In the trigger, to compare the values i have added IF condition to check OLD and NEW values and changed columns i am saving (comma separated) in a column in the same table. Again in POST-QUERY trigger i am applying the visual attributes. Suppose if i alter the main table then i have to modify the trigger accordingly. This is not dynamic. Is there any way we can make this process dynamic?
You probably can make it more dynamic. You can use the data dictionary to see what columns belong to the table and use "execute immediate" to dynamically execute the trigger code.
I would never do it that way for several reasons:
You cannot compile the dynamic code, so you don't know until run time if there is an error in your dynamic pl/sql.
Dynamic pl/sql is always slower than compiled code.
Execute immediate is vulnerable to sql injection if you are not careful.
What you can do is create a generator that generates the trigger code. When you change the table, you just run the generator again and it will create a new trigger.
Sorry for the delay. I am concatinating the columns with comma that are changed. Like column1||','||column2 and while retrieving i am considering one column at a time using SQL functions in post query using a loop to set the Visual Attribute property of the item. Can we make it more dynamic? Any suggestions?