Oracle - Audit Triggers.
Hi Folks,
We have an audit table for most of the transactional tables in our application database. The records in the audit table are being populated with a trigger.
What we capture is the original record in the table along with the username of the person making a change to it.
Example:-
My table is COUNTRY.
The audit table is COUNTRY_AUD.
COUNTRY_AUD has the exact same columns as COUNTRY along with two extra columns. The extra columns are "WHO" and "WHEN". WHO captures the username of the person logged in. The WHEN captures the time when the change was done. Each change (update / delete) to the original record is captured faithfully.
The new requirement is to introduce a new column in the audit table called "WHAT" and capture the client application responsible for making the change.
I know that the view v$session can get information like the program name and machine name where the user logged in from.
If every user has just a single session to the database, this would be an easy task. Since we have multiple applications, each user typically has two or more records in the v$session.
How does my audit trigger come to know, which session is being used for this purpose?