Obtain logged on user in a PL/SQL trigger
by Alex Bocek
I have a trigger created against our address table to capture data manipulation transactions in an audit table as follows:
CREATE OR REPLACE TRIGGER ADVANCE.AU_TR_ADDRESS
AFTER DELETE OR INSERT OR UPDATE ON advance.address
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
Each DELETE OR INSERT OR UPDATE operation is inserted into the audit table
au_audit_address wich has the following initial columns:
On a DELETE, I insert audit values as:
,sys_context('USERENV', 'SESSION_USER') --USERCODE
,sys_context('USERENV', 'OS_USER') --OS_USER
The issue is that (because this is a DELETE) I have no "New:operator". The 'OS_USER' being returned is from ORACLE and is not the "logged_on" operator doing the DELETE operation. So, an inaccurate operator is being populated into the audit table.
Can you suggest a way to capture the true logged on operator/user?