Help needed for BEFORE DELETE Trigger [message #303054] |
Wed, 27 February 2008 16:59  |
rchenna
Messages: 25 Registered: February 2008
|
Junior Member |
|
|
Here is the scenario:
I have a web application. Users need to login to access this application. Oracle 10g is the back end database for this application.
Application User -> Joe
Database schema owner -> focal_prod
Joe logged into the web application and trying to delete an employee from EMP table. I need to audit this action and create a record in EMP_AUDIT table. I need to keep Joe (application user) name in this record under DELETED_BY column. How do I pass Joe name to the database trigger?
Thanks in advance,
Rao
|
|
|
|
|
Re: Help needed for BEFORE DELETE Trigger [message #303224 is a reply to message #303054] |
Thu, 28 February 2008 07:51   |
dr.s.raghunathan
Messages: 540 Registered: February 2008
|
Senior Member |
|
|
hi,
generally one can keep application user and password
in separate table.
Whenever delete option exercised select the application
user from the table on store it in required column.
Hope you would have come across storing user_name and time_stamp
on each record of any table.
this piece of suggestion may help you since i also have overcome
this problem sometime back.
yours
dr.s.raghunathan
|
|
|
|
Re: Help needed for BEFORE DELETE Trigger [message #303226 is a reply to message #303054] |
Thu, 28 February 2008 07:55   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The advantage of the last example in the link posted by Ross is that you don't need anything more than the standard Oracle audit mechanism.
No statement trigger, no new audit table, no new context variable... all is already built-in. You just have to set client identifier.
Regards
Michel
[Updated on: Thu, 28 February 2008 07:56] Report message to a moderator
|
|
|
Re: Help needed for BEFORE DELETE Trigger [message #303231 is a reply to message #303225] |
Thu, 28 February 2008 08:34   |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
dr.s.raghunathan wrote on Thu, 28 February 2008 07:54 | hi
use the following in your trigger
:new.deleted_by := apex_custom_auth.get_user_name;
yours
dr.s.raghunathan
|
Sorry I can’t get it, please explain. This function exists only if APEX is installed, is this always the case? By the way, what Oracle version are we talking about ?
|
|
|
|
|
Re: Help needed for BEFORE DELETE Trigger [message #303387 is a reply to message #303273] |
Fri, 29 February 2008 02:26   |
mnitu
Messages: 159 Registered: February 2008 Location: Reims
|
Senior Member |
|
|
Barbara Boehmer wrote on Thu, 28 February 2008 10:27 | There should not be an underscore in username:
select apex_custom_auth.get_username from dual;
Results
GET_USERNAME
BARBARA_ADMIN
1 rows returned in 0.03 seconds
|
Maybe, but this is still available only if APEX is installed. Does « a web application » always mean APEX application ?
|
|
|
|
|