Home » SQL & PL/SQL » Client Tools » logon trigger creates 2 records (Oracle 10g R2, XP)
logon trigger creates 2 records [message #469786] Thu, 05 August 2010 15:43 Go to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Hi Experts,

Please help me figure out why my logon trigger always creates
2 records with different timestamp. Also is this the proper way of excepting records to insert?

Script.

CREATE OR REPLACE TRIGGER TR_LOGON_AUDIT
AFTER LOGON ON DATABASE
BEGIN
If user<> 'DBSNMP' then -- don't want to insert this
if user <> 'SYS' then -- don't want to insert this

insert into AUD_SESSION_TBL
(
OS_USERNAME,
USERNAME,
USERHOST,
LOGON_TIMESTAMP,
LOGOFF_TIMESTAMP,
ACTION_NAME,
COMMENT_TEXT,
RETURN_CODE
)
VALUES
(
sys_context('USERENV','OS_USER'),
USER,
sys_context('USERENV','HOST'),
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),
NULL,
'LOGON',
SYS_CONTEXT('USERENV','IP_ADDRESS'),
0
);
end if;
end if;
END;
/


Result when select.

OS_USERNAME LOGON_TIMESTAMP
----------- ----------------
GAN 2010-08-05 14:27:52
GAN 2010-08-05 14:27:55

Thank you,
George
Re: logon trigger creates 2 records [message #469793 is a reply to message #469786] Thu, 05 August 2010 16:44 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

If you choose to ignore Posting Guideline, I choose not answer.
Re: logon trigger creates 2 records [message #469796 is a reply to message #469786] Thu, 05 August 2010 17:01 Go to previous messageGo to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
CREATE OR REPLACE TRIGGER TR_LOGON_AUDIT
AFTER LOGON ON DATABASE
BEGIN
	If user<> 'DBSNMP' then 
		if user <> 'SYS' then

			insert into AUD_SESSION_TBL
			(
			OS_USERNAME,
			USERNAME,
			USERHOST,
			LOGON_TIMESTAMP,
			LOGOFF_TIMESTAMP,
			ACTION_NAME,
			COMMENT_TEXT,
			RETURN_CODE
			)
			VALUES
			(
			sys_context('USERENV','OS_USER'),
			USER,
			sys_context('USERENV','HOST'),
			to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),
			NULL,
			'LOGON',
			SYS_CONTEXT('USERENV','IP_ADDRESS'),
			0 
			);
		end if;
	end if;
END;
/



Hi BlackSwan,

Please see reformat of my code.

Thank you
Re: logon trigger creates 2 records [message #469797 is a reply to message #469796] Thu, 05 August 2010 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
how can we reproduce what you report?
Re: logon trigger creates 2 records [message #469798 is a reply to message #469797] Thu, 05 August 2010 17:13 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
The main question here is how are you logging on?
What tool/language are you using?

Because I really can't see any way that trigger can give you 2 records 3 seconds apart unless you actually logged in twice.
Re: logon trigger creates 2 records [message #469816 is a reply to message #469798] Thu, 05 August 2010 22:26 Go to previous messageGo to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Our database is 10g R2 & we're using Toad DBA tools. The Trigger was created in my account (Not GAN) w/c is currently on and inserted the records into a local table in the same account named AUD_SESSION_TBL table. For testing, I logon using other account in this case "GAN" and when I viewed the table it appears two records created. Actually, I noticed that the 2 records has also different SESSIONID (during investigation. not part on the code supplied) aside from the 3 seconds different on LOGON_TIMESTAMP.
Re: logon trigger creates 2 records [message #469817 is a reply to message #469816] Thu, 05 August 2010 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
GAN2009 wrote on Thu, 05 August 2010 20:26
Our database is 10g R2 & we're using Toad DBA tools. The Trigger was created in my account (Not GAN) w/c is currently on and inserted the records into a local table in the same account named AUD_SESSION_TBL table. For testing, I logon using other account in this case "GAN" and when I viewed the table it appears two records created. Actually, I noticed that the 2 records has also different SESSIONID (during investigation. not part on the code supplied) aside from the 3 seconds different on LOGON_TIMESTAMP.


use Oracle AUDIT for testing to confirm reality!
Re: logon trigger creates 2 records [message #469837 is a reply to message #469817] Fri, 06 August 2010 02:00 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you got TOAD configured so that it runs each of its windows in a seperate oracle session?
Re: logon trigger creates 2 records [message #469848 is a reply to message #469816] Fri, 06 August 2010 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
GAN2009 wrote on Fri, 06 August 2010 05:26
Our database is 10g R2 & we're using Toad DBA tools. The Trigger was created in my account (Not GAN) w/c is currently on and inserted the records into a local table in the same account named AUD_SESSION_TBL table. For testing, I logon using other account in this case "GAN" and when I viewed the table it appears two records created. Actually, I noticed that the 2 records has also different SESSIONID (during investigation. not part on the code supplied) aside from the 3 seconds different on LOGON_TIMESTAMP.


If you don't use this b... TOAD but SQL*Plus then you can work professionally and do not waste your time.
TOAD is just a tremendous bug.

Regards
Michel

Re: logon trigger creates 2 records [message #469971 is a reply to message #469848] Fri, 06 August 2010 11:24 Go to previous messageGo to next message
GAN2009
Messages: 25
Registered: January 2009
Location: Phils
Junior Member
Confirmed.

Toad did that intentionally with the option to turn it off.

Thank you!
Re: logon trigger creates 2 records [message #470443 is a reply to message #469971] Mon, 09 August 2010 23:20 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
GAN2009 wrote on Fri, 06 August 2010 11:24
Confirmed.

Toad did that intentionally with the option to turn it off.

Thank you!


1/ From where and How ?
2/ Can you please post the link ( if any)?
Re: logon trigger creates 2 records [message #470720 is a reply to message #469971] Tue, 10 August 2010 09:30 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
Senior Member
GAN2009 wrote on Fri, 06 August 2010 18:24
Confirmed.

Toad did that intentionally with the option to turn it off.

Thank you!

You don't want to turn this off, otherwise a query in one tab will block all further activity.
Simply don't bother about the audit contents.
Re: logon trigger creates 2 records [message #470727 is a reply to message #470720] Tue, 10 August 2010 09:44 Go to previous message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
I always turn it off cause otherwise I end up making changes in one window and wonder why I can't see the results in another.
Previous Topic: Return data from Oracle for Crystal
Next Topic: splitted queries
Goto Forum:
  


Current Time: Tue Jul 29 21:59:34 CDT 2014

Total time taken to generate the page: 0.08721 seconds