axnst2
asked on
Oracle session trigger doesn't seem to execute properly
Hi Experts,
I created a trigger to ALTER the session when a particular user logs on and I don't think that the trigger is being triggered. I created the below trigger under the SYSTEM schema and it didn't work, so then I created it under the user's schema and it didn't work either. I don't think that the trigger is getting executed when the user starts a session. I say that because if I run the same exact code using the same user during a SQL*PLUS session, it works great! What I mean is that If I connect as the user via SQL*PLUS and I alter my session I am able to verify that the session was altered. However, after creating the trigger, if I connect via SQL*PLUS as the same user, not alter my session (trying to rely on the trigger to alter the session) I am not able to verify that the session was altered. What am I doing wrong? Again, I have tried creating the trigger under both the SYSTEM and the user's schema.
Here's the trigger:
Thanks!
I created a trigger to ALTER the session when a particular user logs on and I don't think that the trigger is being triggered. I created the below trigger under the SYSTEM schema and it didn't work, so then I created it under the user's schema and it didn't work either. I don't think that the trigger is getting executed when the user starts a session. I say that because if I run the same exact code using the same user during a SQL*PLUS session, it works great! What I mean is that If I connect as the user via SQL*PLUS and I alter my session I am able to verify that the session was altered. However, after creating the trigger, if I connect via SQL*PLUS as the same user, not alter my session (trying to rely on the trigger to alter the session) I am not able to verify that the session was altered. What am I doing wrong? Again, I have tried creating the trigger under both the SYSTEM and the user's schema.
Here's the trigger:
create or replace
TRIGGER "ACTIVETRACK"."ON_LOGON_ACTIVETRACK"
AFTER LOGON ON DATABASE
WHEN ( USER = 'ACTIVETRACK' )
BEGIN
execute immediate 'ALTER SESSION set nls_sort=BINARY_CI scope BOTH';
execute immediate 'ALTER SESSION set nls_comp=ansi scope BOTH';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Thanks!
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
It was bombing on the SCOPE section of the ALTER SESSION commands! Changed it and now it works like a charm! Thanks!