Logon trigger not firing

Lisa McGrath

unread,

Nov 21, 2002, 2:09:33 PM11/21/02

to oracle-...@uwex.edu, mad...@yahoogroups.com

I'm trying to create a simple logon trigger but it never seems to fire.
We're running Oracle 8.1.7.3.0 on a UNIX AIX system.

The trigger I'm trying to create is:

CREATE OR REPLACE TRIGGER set_id_on_logon
AFTER logon ON DATABASE
BEGIN
insert into uwsias.rls_trigger_test
values (user, 'Executing logon trigger',sysdate);
END;

I have already created the uwsias.rls_trigger_test table and have
granted all on the table to PUBLIC and directly to SYS, SYSTEM, and
CSILVA (a test id). I've created this trigger both as SYS and as
SYSTEM, and have created it both as ON DATABASE trigger and ON
CSILVA.SCHEMA trigger. Nothing seems to work.

The trigger created ok, is valid and enabled. When I logon as CSILVA I
connect ok - no 'insufficient privileges' or other error message, but
no record is inserted either. Am I missing a step? Does the database
need to be restarted before a logon trigger will take effect? Some
parameter that needs to be set?

Thanks for any help.

Lisa
--
Lisa McGrath
Lmcg...@uwsa.edu (608) 263-4535
Office of Information Services
University of Wisconsin System Administration
--

Lisa McGrath

unread,

Nov 21, 2002, 2:08:58 PM11/21/02

to oracle-...@uwex.edu, mad...@yahoogroups.com

Peter Sylvester

unread,

Nov 21, 2002, 4:45:40 PM11/21/02

to

Well, for one thing, I don't see this committed anywhere...

--Peter

Karsten Farrell

unread,

Nov 21, 2002, 5:07:56 PM11/21/02

to

Before you just commit, make it an autonomous transaction (so you don't
commit more than you thought you were). It shouldn't matter (caveat) -
since they are just now logging in, they should *not* have any other
transactions they might want to rollback later. I haven't tested it, but
does the logon trigger fire when I do a remote 'SELECT...FROM
table@server_where_logon_trigger_lives'?

Lisa McGrath

unread,

Nov 21, 2002, 5:51:06 PM11/21/02

to

Peter,

Thanks for the comment. I was doing a commit as soon as I logged in, and
then I changed the trigger to do an "execute immediate 'commit';" right after
the insert. Neither made any difference.

Lisa

Lisa McGrath

unread,

Nov 21, 2002, 5:52:52 PM11/21/02

to

Karsten,

Thanks for the suggestion. I haven't tried logging in by doing a remote
call. I'll create a db_link & give it a try.

Lisa

Paul Brewer

unread,

Nov 21, 2002, 6:13:18 PM11/21/02

to

"Lisa McGrath" <lmcg...@uwsa.edu> wrote in message
news:3DDD2F6D...@uwsa.edu...

Lisa,

Drop, disable or revoke the DBA role and try again.
Please post results.

HTH,
Paul

srivenu

unread,

Nov 24, 2002, 11:48:17 PM11/24/02

to

See if this parameter is set to FALSE
_system_trig_enabled
regards
Srivenu

Paul Brewer

unread,

Nov 25, 2002, 2:51:49 PM11/25/02

to

"srivenu" <sri...@hotmail.com> wrote in message
news:1a68177.02112...@posting.google.com...

Good catch, Srivenu! (see other thread).
I've recommended OP should avoid undocumented parameters.

Regards,
Paul

James Williams

unread,

Mar 9, 2003, 8:02:07 PM3/9/03

to

On Thu, 21 Nov 2002 13:08:58 -0600, Lisa McGrath <lmcg...@uwsa.edu>
wrote:

I can't remember the execat init.ora parm you add during maintenance.
Could you have done something like _system_trigger_enabled = false to
install 8.1.7.3 patch and forgot to take it out?