Home » SQL & PL/SQL » SQL & PL/SQL » Usage of Context variables inside the trigger (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit,PL/SQL Release 10.2.0.3.0 CORE 10.2.0.3.0,TNS for Solaris: Version 10.2.0.3.0,NLSRTL Version 10.2.0.3.0)
Usage of Context variables inside the trigger [message #443877] Wed, 17 February 2010 16:51 Go to next message
sam5127
Messages: 17
Registered: April 2007
Junior Member
Hi All,

Am trying to use the SYS_CONTEXT variables inside a trigger. After reading the documentation I found that the below query
when used inside the trigger code will return the trigger owner.
Select Username from User_Users


Will any of the below variables return the trigger owner when used inside the trigger code
sys_context('USERENV','CURRENT_USER')
or
sys_context('USER','CURRENT_SCHEMA')


Thanks in advance,
Sam
Re: Usage of Context variables inside the trigger [message #443886 is a reply to message #443877] Wed, 17 February 2010 19:16 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
If you already have a solution
(when used inside the trigger code will return the trigger owner -> Select Username from User_Users)

why do you bother asking the following question?

Will any of the below variables return the trigger owner when used inside the trigger code

Why ask the question here instead of just doing some minor testing to see the actual results yourself?
Re: Usage of Context variables inside the trigger [message #444030 is a reply to message #443877] Thu, 18 February 2010 07:06 Go to previous messageGo to next message
sam5127
Messages: 17
Registered: April 2007
Junior Member
Hi Blackswan

Firstly, Thanks for the reply.

why do you bother asking the following question?


Reason 1:
We have a Java UI which uses the oracle database for pretty much everything (Authentication, Auditing, ....etc) and our page load cutoff time is under 3 sec. Using the select from user_users I can see a performance hit.

Test: Inserting 10,000 rows into the table
Actual time (2 Sec)

1.Using the select statement inside the trigger (9 Sec)
2.Using the sys_context('USER','CURRENT_SCHEMA') (3 Sec)

Reason 2:
I did some testing and it is in fact returning the same result, but wanted to be sure before going ahead and changing around 1200 triggers.

Is there any chance that both the queries return different results when used inside a trigger

1.Select username from user_users
2.Sys_context('USER','CURRENT_SCHEMA')

(The trigger owner and the underlying table owner are different)

Re: Usage of Context variables inside the trigger [message #444032 is a reply to message #444030] Thu, 18 February 2010 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The trigger owner and the underlying table owner are different

What a bad design!
What will happen when you wil export the schema?
You like the problems, don't you.

Regards
Michel
Re: Usage of Context variables inside the trigger [message #444047 is a reply to message #444030] Thu, 18 February 2010 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>changing around 1200 triggers.

My initial reaction is that I suspect a DB independent application which is enforcing integrity constraint at application level.

Am I correct?
Re: Usage of Context variables inside the trigger [message #444072 is a reply to message #443877] Thu, 18 February 2010 11:03 Go to previous messageGo to next message
sam5127
Messages: 17
Registered: April 2007
Junior Member
Quote:

The trigger owner and the underlying table owner are different

What a bad design!
What will happen when you wil export the schema?
You like the problems, don't you.

Regards
Michel


We are implementing a schema re-architecture. At any point of time only one of the schemas will be active and the switching mechanism is through a CPR switch which is seamless. As part of the design the base tables only exist in one schema and on the cloned schema we have either materialized views or synonyms.

So we have triggers in the cloned schema(for the synonyms in the cloned schema) on the original schema base tables.

You have a better solution, Please pardon my ignorance and let me know.

We already went down the oracle replication and virtualization paths.



Quote:
My initial reaction is that I suspect a DB independent application which is enforcing integrity constraint at application level.

Am I correct?


The CPR switch is DB independent and the current active schema is set using a table which stores the active schema name.

So we are trying to control the duplicate execution of the trigger code on the inactive schema. (manually disabling is not an option)

Creating a disabled trigger (Am not sure if its possible prior to 11g)

So for now we are setting context variable on login and checking it inside the trigger. And execute only if the context variable is same as the trigger owner.

So to get the trigger owner inside the trigger is the original issue.

So again to the same old question :-
Is there any chance that both the queries return different results when used inside a trigger

1.Select username from user_users
2.Sys_context('USER','CURRENT_SCHEMA')

((((((((((((((or))))))))))))))))))))))))))

any other way to get the trigger owner inside the trigger with minimum performance impact.





Re: Usage of Context variables inside the trigger [message #444075 is a reply to message #444072] Thu, 18 February 2010 11:10 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>any other way to get the trigger owner inside the trigger with minimum performance impact.

Since you need to modify the trigger code anyways, what about HARDCODING into a trigger variable the trigger owner as part of trigger CREATE process?
Then the "lookup" would be instantaneous.

[Updated on: Thu, 18 February 2010 11:10]

Report message to a moderator

Re: Usage of Context variables inside the trigger [message #444079 is a reply to message #443877] Thu, 18 February 2010 11:20 Go to previous messageGo to next message
sam5127
Messages: 17
Registered: April 2007
Junior Member
All the non table code (procedure, packages....triggers) are imported into the cloned schema using datapump import export.

So we need to have the same set of code, which is intelligent enough to work on both the schemas.
Re: Usage of Context variables inside the trigger [message #444086 is a reply to message #443877] Thu, 18 February 2010 11:42 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Having done a bit of testing it appears that:
1) Sys_context('USER','CURRENT_SCHEMA')
does exactly what it says and gives the schema the trigger is in.
2) Select username from user_users
Gives the username if it's the same as the table owner and null otherwise. Which surprises me - would have thought you'd get the non-null for the schema that issued the insert.

Tom Kytes who_called_me procedure gives the same result as sys_context.

As for a better solution we would really need to know why you've got triggers in any schema other than the tables in the first place.


EDIT: fixed link

[Updated on: Fri, 19 February 2010 03:47]

Report message to a moderator

Re: Usage of Context variables inside the trigger [message #444134 is a reply to message #443877] Thu, 18 February 2010 15:56 Go to previous messageGo to next message
sam5127
Messages: 17
Registered: April 2007
Junior Member
Thanks a lot for the reply cookiemonster.

Quote:
1) Sys_context('USER','CURRENT_SCHEMA')
does exactly what it says and gives the schema the trigger is in.


So the above code always gives the trigger owner(schema the trigger is in)??, if yes, then it might do the work for me.

Re: Usage of Context variables inside the trigger [message #444136 is a reply to message #443877] Thu, 18 February 2010 16:11 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I can tell yes.
Re: Usage of Context variables inside the trigger [message #444143 is a reply to message #444136] Thu, 18 February 2010 20:52 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Just to add the link ....
Who_called_me Procedure

As the link specified was broken....

sriram Smile
Re: Usage of Context variables inside the trigger [message #444153 is a reply to message #443877] Thu, 18 February 2010 23:02 Go to previous messageGo to next message
sam5127
Messages: 17
Registered: April 2007
Junior Member
Thank you cookiemonster for the input. Really appreciate it.

Thanks for the link sriram.
Re: Usage of Context variables inside the trigger [message #444187 is a reply to message #444143] Fri, 19 February 2010 03:47 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
ramoradba wrote on Fri, 19 February 2010 02:52
Just to add the link ....
Who_called_me Procedure

As the link specified was broken....

sriram Smile


Thanks for that, I've fixed the link in my own message now.
Previous Topic: reference to learn PL / SQL in oracle for beginners (merged by CM)
Next Topic: ORA-01801: date format is too long for internal buffer
Goto Forum:
  


Current Time: Mon Sep 26 07:50:43 CDT 2016

Total time taken to generate the page: 0.17934 seconds