Getting Oracle user id of app user deleting a record in trigger
561776Aug 3 2009 — edited Aug 3 2009I am writing some triggers and one of them is a Before Delete Trigger. I am wanting to retrieve the UserID of the logged on user so I can record who actually deleted the record.
Obviously with a delete trigger the values in :new don't exist so i can't go that route.
I have been looking into the sys_context('USERENV',........) function and have tried using CURRENT_USERID , PROXY_USERID , SESSION_USERID even OS_USER with a cursor to get the related UserID from FND_USER.
None of these options seem to give me the User ID associated with MY user logon. They return users such as Oracle , APPS etc
Imagine that a user CDENSLOW logs into Oracle 11i e-Business suite. They delete a record from a table and save it. I want my trigger code to be able to record the userid of CDENSLOW from FND_USER so that I know that it is this user that has done the delete.
As I said above I'm not meeting with a lot of success.
Can you please tell me if this is possible and how it would be done in PL/SQL?
Note that we have not implemented Single Sign On in our 11i system. We also access e-Business suite through Citrix.