We have converting an existing non-apex application to Apex 20.1 application and they currently have auditing setup using two tables. The main audit table:
audit_id NUMBER
audit_message VARCHAR2
reason_for_change VARCHAR2
Then there is an audit_detail table:
Audit_detail_id NUMBER(PK)
Audit_id NUMBER(FK)
ColumnName VARCHAR2
OLD_VALUE VARCHAR2
NEW_VALUE VARCHAR2
There are created_by, created_on columns as well but left them out for simplicity sake. We are currently auditing in the triggers with BEFORE INSERT OR UPDATE FOR EACH ROW. This all works fine in the auditing all occurs in one table.
The problem comes when we need to insert into audit_detail from multiple tables/triggers. The requirement states there should be one entry in the audit table for the main audit event i.e. "Configured New XXXXXXX" and then the audit_details will contain all the entries that make up "XXXXXXX" - which could be in 1 - X tables.
So when we create the main audit entry I need to be able to store that ID in a global variable/session key so that when the triggers fire in the child tables it knows how to link back to the main audit entry.
As stated above this is from Apex application so wondering if we could set the auditId in a :P0_AUDITID page item from the main audit trigger or use a pkg global variable or apex_session entry?
The other problem is if we do use some P0 item, pkg global variable or session item, we have to know when to clear it out after the last transaction occurs? Since Apex is sending in the DML how will we know when the last transaction happens so we can clear out the auditId?