Posted on 2009-05-14
I a have an after update or delete trigger for auditing several tables.
Audit data is saved into this table.
( timestamp date,
Tables are get audited by web application (oracle mod_plsq), client server PB application and TOAD, winsql, etc.
applications are using one DB account and users log in using application accounts.
The question is how to best capture the "userid" and "ip address" of the user doing the update or delete.
Some table do have "last_modified_by" and some do not. Trigger can only access the OLD and NEW values of the ROW.
If the column is not getting updated or table does not have it i cant get it. Also on deletes the "OLD.USERID" would not have the userid that deleted the record.
For ip address, it seems there are different environment variables for WEB and client/server connection. When i checked for the "owa_util.get_cgi_env" in stored procedure, the sql*plus session resutled in error on update.
What would be the best way to accomplish this for web and client server environment?
would setting a context after logging work for both. any code samples.