| Usage of Context variables inside the trigger [message #443877] |
Wed, 17 February 2010 16:51  |
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 #444030 is a reply to message #443877] |
Thu, 18 February 2010 07:06  |
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 #444072 is a reply to message #443877] |
Thu, 18 February 2010 11:03 |
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 #444086 is a reply to message #443877] |
Thu, 18 February 2010 11:42 |
cookiemonster
Messages: 13967
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 |
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.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
