Link to home
Create AccountLog in
Avatar of axnst2
axnst2Flag for United States of America

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:

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;

Open in new window



Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of axnst2

ASKER

Crap...I should have thought of that!

It was bombing on the SCOPE section of the ALTER SESSION commands!  Changed it and now it works like a charm!  Thanks!