Home » SQL & PL/SQL » SQL & PL/SQL » Help needed for BEFORE DELETE Trigger
Help needed for BEFORE DELETE Trigger [message #303054] Wed, 27 February 2008 16:59 Go to next message
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 #303067 is a reply to message #303054] Wed, 27 February 2008 20:59 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There's some information here that might help
Re: Help needed for BEFORE DELETE Trigger [message #303223 is a reply to message #303054] Thu, 28 February 2008 07:46 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Maybe you can use oracle application context in order to trasmit the application user where this information is nedded via sys_context.
Re: Help needed for BEFORE DELETE Trigger [message #303224 is a reply to message #303054] Thu, 28 February 2008 07:51 Go to previous messageGo to next message
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 #303225 is a reply to message #303054] Thu, 28 February 2008 07:54 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi

use the following in your trigger

:new.deleted_by := apex_custom_auth.get_user_name;


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #303236 is a reply to message #303225] Thu, 28 February 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
@dr.s.raghunathan,
SQL> select apex_custom_auth.get_user_name from dual;
select apex_custom_auth.get_user_name from dual
       *
ERROR at line 1:
ORA-00904: "APEX_CUSTOM_AUTH"."GET_USER_NAME": invalid identifier

More explainations are needed.

Regards
Michel
Re: Help needed for BEFORE DELETE Trigger [message #303273 is a reply to message #303236] Thu, 28 February 2008 10:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
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


Re: Help needed for BEFORE DELETE Trigger [message #303387 is a reply to message #303273] Fri, 29 February 2008 02:26 Go to previous messageGo to next message
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 ?
Re: Help needed for BEFORE DELETE Trigger [message #303391 is a reply to message #303273] Fri, 29 February 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> select apex_custom_auth.get_username from dual;
select apex_custom_auth.get_username from dual
       *
ERROR at line 1:
ORA-00904: "APEX_CUSTOM_AUTH"."GET_USERNAME": invalid identifier

With or without underscore, I get the same result.

In addtion, a search on Oracle 10.2 documentation: http://www.oracle.com/pls/db102/search?remark=quick_search&word=APEX_CUSTOM_AUTH&tab_id=&format=ranked returns "Your search term APEX_CUSTOM_AUTH did not match any topics".

Regards
Michel

[Updated on: Fri, 29 February 2008 02:40]

Report message to a moderator

Re: Help needed for BEFORE DELETE Trigger [message #303392 is a reply to message #303391] Fri, 29 February 2008 02:43 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Ok, I found in 11g documentation: http://www.oracle.com/pls/db111/ranked?word=APEX_CUSTOM_AUTH&remark=federated_search but OP has 10g.

Regards
Michel
Previous Topic: Invalid text is stored which is > 4000 length in nclob field
Next Topic: Missing right parenthesis (split)
Goto Forum:
  


Current Time: Thu Feb 13 22:32:41 CST 2025