I have a task where I need to log field level changes to a number of ADF BC entities. The JDeveloper is 12.2.1.3 and the database is Oracle Database 12.2.0.1.0.
The approach is as follows. I create triggers on each underlining tables, which track new and old values and if they differ log the data into the audit table.
The audit table has the following structure
row_id, task_id, field_name, new_value, old_value, last_updated_by and last_updated_date
The question here is how to link the trigger to user and task details from ADF side. I need to have task_id (some identifier which links a task table with all other tables), last_updated_by, last_updated_date on database side. To have such data, I am planning to use prepareSession method, where I call a database procedure and using dbms_session.set_context set all those values.
We are using a single datasource for a number of applications. Is this approach a valid one? Will I have a consistent with pooling, passivation or other lifecycle events?