Home » SQL & PL/SQL » SQL & PL/SQL » Problem in database trigger (oracle 10g)
Problem in database trigger [message #353476] Tue, 14 October 2008 00:44 Go to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
Hi All , Can anybody tell me why this is not working , I am trying to stop export and import in my server throgh command prompt(exp,imp). this trigger is not executing when we connect user in exp command through command prompt.



create or replace trigger prevent_exp_imp
after logon on database
begin
if user='TEST' and upper(sys_context('userenv','module')) in ('EXP.EXE','IMP.EXE') then
raise_application_error (-20001, 'You are not allowed to export or import data.');
end if;
end;


pls if anybody know the correct ans please tell me.
Re: Problem in database trigger [message #353478 is a reply to message #353476] Tue, 14 October 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
MODULE is not the correct parameter to check, it is PROGRAM which is not in USERENV namespace so you have to query v$session.

Regards
Michel
Re: Problem in database trigger [message #353479 is a reply to message #353478] Tue, 14 October 2008 00:48 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
i also checked from v$session but its not working i dont know why this trigger is not executing?
Re: Problem in database trigger [message #353486 is a reply to message #353479] Tue, 14 October 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As you didn't post what you actually did, we can't know what "its not working" means.

Regards
Michel
Re: Problem in database trigger [message #353487 is a reply to message #353476] Tue, 14 October 2008 00:51 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
When i run this command in toad
select upper(sys_context('userenv','module')) from dual
It returns toad (version)

When i run this command in sqlplus
select upper(sys_context('userenv','module')) from dual
It returns sql*plus


then it has to return exp.exe in command prompt
Re: Problem in database trigger [message #353489 is a reply to message #353476] Tue, 14 October 2008 00:54 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
means this trigger is not executing when we use exp command in command promt


start>run>cmd>exp
user/password@database

This trigger has to fire at that time ok?
Re: Problem in database trigger [message #353491 is a reply to message #353489] Tue, 14 October 2008 00:58 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Did you never learn to debug code? There are several ways to _make sure_ instead of guess what a value of a certain variable is at runtime.
Some possibilities: raise an error unconditionally, with the value recognizable in the error message (not advisable on databases you share with others), autonomous transaction procedure that writes to a log-table, etc.
Re: Problem in database trigger [message #353492 is a reply to message #353487] Tue, 14 October 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
manudu20 wrote on Tue, 14 October 2008 07:51
When i run this command in toad
select upper(sys_context('userenv','module')) from dual
It returns toad (version)

When i run this command in sqlplus
select upper(sys_context('userenv','module')) from dual
It returns sql*plus


then it has to return exp.exe in command prompt


WRONG!
Is "toad" "TOAD.EXE"?
Is "SQL*Plus" "SQLPLUS.EXE"?

Regards
Michel

[Updated on: Tue, 14 October 2008 01:00]

Report message to a moderator

Re: Problem in database trigger [message #353494 is a reply to message #353476] Tue, 14 October 2008 01:00 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
what wrong ?
Re: Problem in database trigger [message #353496 is a reply to message #353489] Tue, 14 October 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
manudu20 wrote on Tue, 14 October 2008 07:54
means this trigger is not executing when we use exp command in command promt


start>run>cmd>exp
user/password@database

This trigger has to fire at that time ok?

"user" is not "TEST"

If you want help, you MUST post ALL what you did, INCLUDING trigger creation from which user.

Regards
Michel

Re: Problem in database trigger [message #353497 is a reply to message #353491] Tue, 14 October 2008 01:01 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
can u help me on this topic and about debugging code?
Re: Problem in database trigger [message #353498 is a reply to message #353494] Tue, 14 October 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
manudu20 wrote on Tue, 14 October 2008 08:00
what wrong ?

Quote:
then it has to return exp.exe in command prompt

Regards
Michel

Re: Problem in database trigger [message #353501 is a reply to message #353497] Tue, 14 October 2008 01:04 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
manudu20 wrote on Tue, 14 October 2008 08:01
can u help me on this topic and about debugging code?

Sure, if you can show us what you tried. For example, did you Google? There are some good search words in my text. (debug autonomous Oracle) would be a nice set..
Re: Problem in database trigger [message #353502 is a reply to message #353498] Tue, 14 October 2008 01:06 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
1.I have a duser "edde/edde" In databse named "Orcl"

2.I had created this trigger after login on "edde"
create or replace trigger prevent_exp_imp
after logon on database
begin
if user='edde' and upper(sys_context('userenv','module')) in ('EXP.EXE','IMP.EXE') then
raise_application_error (-20001, 'You are not allowed to export or import data.');
end if;
end;
/

3. but when i connect user edde in exp command prompt this trigger would not fire can u tell me the reason why so?
Re: Problem in database trigger [message #353506 is a reply to message #353502] Tue, 14 October 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Are you sure "egde" is in lower case?

And as I said "module" can't help you.

Regards
Michel
Re: Problem in database trigger [message #353507 is a reply to message #353506] Tue, 14 October 2008 01:16 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
then what should i use?
Re: Problem in database trigger [message #353512 is a reply to message #353507] Tue, 14 October 2008 01:27 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
this summary should help, but the hints where already given:

1. start an exp without giving a tablename, it waits for Your "debugging"
    exp edde/edde@<instance>

2. start a TOAD / SQLplus and test the result of
  SELECT osuser, machine, program, module FROM v$session 

3. correct Your trigger, could help to use UPPER() / LOWER() - function
Re: Problem in database trigger [message #353513 is a reply to message #353512] Tue, 14 October 2008 01:30 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
but how can we know the current session.
Re: Problem in database trigger [message #353521 is a reply to message #353513] Tue, 14 October 2008 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
manudu20 wrote on Tue, 14 October 2008 08:30
but how can we know the current session.

By checking all sessions from EDGE, isn't it obvious?

Regards
Michel
Re: Problem in database trigger [message #353523 is a reply to message #353521] Tue, 14 October 2008 01:56 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Michel Cadot wrote on Tue, 14 October 2008 08:53
manudu20 wrote on Tue, 14 October 2008 08:30
but how can we know the current session.

By checking all sessions from EDGE, isn't it obvious?

Regards
Michel


EDDE, not EDGE.
The db-user is edde, according to one of the previous messages
Re: Problem in database trigger [message #353526 is a reply to message #353523] Tue, 14 October 2008 02:05 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
thx i will try by v$session and tell u abt this..
Re: Problem in database trigger [message #353533 is a reply to message #353526] Tue, 14 October 2008 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Try it but don't use IM speak.

Regards
Michel
Re: Problem in database trigger [message #353751 is a reply to message #353533] Wed, 15 October 2008 00:38 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
HI ,
i ALSO TRIED WITH V$SESSION BUT THE TRIGGER IS NOT FIRING YET.
Re: Problem in database trigger [message #353757 is a reply to message #353751] Wed, 15 October 2008 00:45 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Did you read your post before hitting Submit?
Did you notice anything?
Re: Problem in database trigger [message #353759 is a reply to message #353757] Wed, 15 October 2008 00:48 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
which post ..?
Can you help me to write trigger "To stop export /import/ Toad " In Server?
Re: Problem in database trigger [message #353762 is a reply to message #353759] Wed, 15 October 2008 00:52 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
The post where your Caps-Lock key was stuck. Have the courtesy to correct that before you blindly hit Submit.
Posting in capitals is considered shouting in netiquette.

As for your question: we can't see where you failed if you don't post your code.
Re: Problem in database trigger [message #353763 is a reply to message #353762] Wed, 15 October 2008 00:54 Go to previous messageGo to next message
manudu20
Messages: 45
Registered: August 2008
Location: Mumbai
Member
i am sorry for that.
Re: Problem in database trigger [message #353769 is a reply to message #353759] Wed, 15 October 2008 01:12 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Can you help me to write trigger "To stop export /import/ Toad " In Server?

Although we cannot say anything about your code until you post it you have to know that if someone really wants to use these tools you can't prevent from this with a trigger as he can rename the program as he wants.

Regards
Michel
Previous Topic: procedure execution
Next Topic: while executing procedure : Savepoint never established
Goto Forum:
  


Current Time: Mon Nov 11 02:00:54 CST 2024